一对多数据显示成一行
GROUP_CONCAT(expr)
1、涉及的表关系:teacher表、teacher_subject_rel表(教师所能教的学科表)、subject表 2、业务场景: 需要拉取所有教师的编号(teacher_no)、学科名(subject_name)。   教师表(teacher)和学科(teacher_subject_rel)是一对多关系, 往往查询出现的是同一教师多条 数据。我们希望得到每个教师一条数据 学科拼接成一条
1、基本语法
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'] )
2、例子
SELECT t.teacher_id as '教师id', t.teacher_no '教师编号', ( SELECT GROUP_CONCAT(s.subject_name) FROM teacher_subject_rel tsr LEFT JOIN `subject` s ON tsr.subject_id = s.subject_id WHERE t.teacher_id = tsr.teacher_id) AS '学科'FROM teacher t
子查询、查询临时表、EXISTS
例子
SELECT *FROM ( SELECT o.id, o.student_intention_id, s. NAME, s.area_id, a.area_name, s.exam_year, o. STATUS, CASE o. STATUS WHEN '1' THEN '待提交' WHEN '2' THEN '待指派' WHEN '3' THEN '已完成' WHEN '4' THEN '处理中' END statusName, CASE o.emergency_degree WHEN '1' THEN '正常' WHEN '2' THEN '紧急' WHEN '3' THEN '非常紧急' END emergencyDegreeName, o.emergency_degree, o.update_time, ( SELECT first_lesson_time FROM jx_strategy WHERE jx_lesson_plan_order_id = o.id AND STATUS IN (2, 7) AND first_lesson_time > now() ORDER BY first_lesson_time ASC LIMIT 1 ) AS first_time, ( SELECT deal_user_id FROM jx_strategy WHERE jx_lesson_plan_order_id = o.id AND STATUS <> 7 AND deal_user_id <> 0 ORDER BY id DESC LIMIT 1 ) AS deal_user_idFROM jx_lesson_plan_order oLEFT JOIN student s ON s.student_intention_id = o.student_intention_idLEFT JOIN area a ON s.area_id = a.idWHERE o. 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 aWHERE 1 = 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 * FROM jx_strategy js WHERE js.jx_lesson_plan_order_id = a.id AND js. STATUS IN (2, 7) AND js.subject_id IN (2, 3))ORDER BY a.update_time DESCLIMIT 0, 10
update 关联变量条件修改
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
update 根据条件做特定的更新
1、涉及的表关系:gzy表中的department、group两个字 2、业务场景：将gzy表的department值是1的改成11 值是2的改成22gorup值是1的改成11 值是2的改成21 其余改成9
UPDATE gzySET department = CASEWHEN department = '1' THEN '11'WHEN department = '2' THEN '22'WHEN department = '3' THEN '33'END, `group` = CASEWHEN `group` = '1' THEN '11'WHEN `group` = '2' THEN '21'ELSE '9'END;
