這篇文章帶給大家的內容是關於mysql中關聯變數條件修改、查詢以及資料顯示成一行的介紹(附程式碼),有一定的參考價值,有需要的朋友可以參考一下,希望對你有所幫助。
一對多資料顯示成一行
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_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-17 00:00:00' AND a.first_time <p><strong>update 關聯變數條件修改</strong></p><p>1、涉及的表關係: user_info表中的id_number(身分證號) teacher表中的birth字段、 關聯關係usrer_id = teacher_id<br>2、業務場景:獲取用戶身份證上的出生日期將出生日期更新在birth字段</p><pre class="brush:php;toolbar:false">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
以上是mysql中關聯變數條件修改、查詢以及資料顯示成一行的介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章!