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、例子

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

mysql中關聯變量條件修改、查詢以及數據顯示成一行的介紹

子查詢、查詢臨時表、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_id FROM     jx_lesson_plan_order o LEFT JOIN student s ON s.student_intention_id = o.student_intention_id LEFT JOIN area a ON s.area_id = a.id WHERE     o. STATUS <> 1 AND s.phone = '18501665888' AND o.emergency_degree = 1 AND o. STATUS = 2 AND 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 a WHERE     1 = 1 AND a.deal_user_id = 145316 AND 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 DESC LIMIT 0,  10

update 關聯變量條件修改

1、涉及的表關系: ?user_info表中的 id_number(身份證號) ? teacher表中的birth字段、 關聯關系usrer_id = teacher_id
2、業務場景:獲取用戶身份證上的出生日期將出生日期更新在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 t INNER JOIN user_info u ON u.user_id = t.teacher_id  ) info on info.teacher_id = t.teacher_id SET t.birth = info.birth1 WHERE 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

? ? ? ??

? 版權聲明
THE END
喜歡就支持一下吧
點贊15 分享