mysql中关联变量条件修改、查询以及数据显示成一行的介绍

本篇文章给大家带来的内容是关于mysql中关联变量条件修改、查询以及数据显示成一行的介绍（附代码），有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

一对多数据显示成一行

GROUP_CONCAT(expr)

1、涉及的表关系:teacher表、teacher_subject_rel表(教师所能教的学科表)、subject表2、业务场景: 需要拉取所有教师的编号(teacher_no)、学科名(subject_name)。 &nbsp 教师表(teacher)和学科(teacher_subject_rel)是一对多关系,往往查询出现的是同一教师多条数据。我们希望得到每个教师一条数据 学科拼接成一条

1、基本语法

group_concat( [DISTINCT]要连接的字段[Order BY 排序字段 ASC/DESC][Separator '分隔符'] )

2、例子SELECTt.teacher_id as '教师id',t.teacher_no '教师编号', (SELECTGROUP_CONCAT(s.subject_name)FROMteacher_subject_rel tsrLEFT JOIN `subject` s ON tsr.subject_id = s.subject_idWHEREt.teacher_id = tsr.teacher_id) AS '学科'FROMteacher t

子查询、查询临时表、EXISTS

例子SELECT*FROM(SELECTo.id,o.student_intention_id,s. NAME,s.area_id,a.area_name,s.exam_year,o. STATUS,CASE o. STATUSWHEN '1' THEN'待提交'WHEN '2' THEN'待指派'WHEN '3' THEN'已完成'WHEN '4' THEN'处理中'END statusName,CASE o.emergency_degreeWHEN '1' THEN'正常'WHEN '2' THEN'紧急'WHEN '3' THEN'非常紧急'END emergencyDegreeName,o.emergency_degree,o.update_time,(SELECTfirst_lesson_timeFROMjx_strategyWHEREjx_lesson_plan_order_id = o.idAND STATUS IN (2, 7)AND first_lesson_time > now()ORDER BYfirst_lesson_time ASCLIMIT 1) AS first_time,(SELECTdeal_user_idFROMjx_strategyWHEREjx_lesson_plan_order_id = o.idAND STATUS <> 7AND deal_user_id <> 0ORDER BYid DESCLIMIT 1) AS deal_user_idFROMjx_lesson_plan_order oLEFT JOIN student s ON s.student_intention_id = o.student_intention_idLEFT JOIN area a ON s.area_id = a.idWHEREo. STATUS <> 1AND s.phone = '18501665888'AND o.emergency_degree = 1AND o. STATUS = 2AND s.exam_year = '2015'AND o.update_time >= '2018-08-14 20:28:55'AND o.update_time <= '2018-08-14 20:28:55') AS aWHERE1 = 1AND a.deal_user_id = 145316AND a.first_time >= '2018-08-17 00:00:00'AND a.first_time <= '2018-08-30 00:00:00'AND EXISTS (SELECT*FROMjx_strategy jsWHEREjs.jx_lesson_plan_order_id = a.idAND js. STATUS IN (2, 7)AND js.subject_id IN (2, 3))ORDER BYa.update_time DESCLIMIT 0, 10update 关联变量条件修改

1、涉及的表关系:user_info表中的 id_number(身份证号)teacher表中的birth字段、 关联关系usrer_id = teacher_id2、业务场景：获取用户身份证上的出生日期将出生日期更新在birth字段UPDATE teacher t INNER JOIN (SELECT t.teacher_id, t.birth, u.id_number, CONCAT(SUBSTRING(u.id_number, 7, 4), '-', SUBSTRING(u.id_number, 11, 2), '-', SUBSTRING(u.id_number, 13, 2)) as birth1, u.reg_date, t.exit_time from teacher tINNER JOIN user_info u ON u.user_id = t.teacher_id) info on info.teacher_id = t.teacher_idSET t.birth = info.birth1WHERE info.reg_date > '2018-08-20 00:00:00' and info.id_number is not NULL and (info.birth is NULL or t.birth = '') and t.is_train = 1以上就是mysql中关联变量条件修改、查询以及数据显示成一行的介绍的详细内容，更多请关注小潘博客其它相关文章！