前言
mysql的語法相信對大家來說都不是難事，但是本文主要給分享了一些mysql復(fù)雜用法的相關(guān)內(nèi)容，通過這篇文章相信大家會對mysql更深的了解一些，下面話不多說了，來一起看看詳細(xì)的介紹吧
一對多數(shù)據(jù)顯示成一行
GROUP_CONCAT(expr)
1、涉及的表關(guān)系:teacher表、teacher_subject_rel表(教師所能教的學(xué)科表)、subject表
2、業(yè)務(wù)場景: 需要拉取所有教師的編號(teacher_no)、學(xué)科名(subject_name)。 教師表(teacher)和學(xué)科(teacher_subject_rel)是一對多關(guān)系, 往往查詢出現(xiàn)的是同一教師多條 數(shù)據(jù)。我們希望得到每個教師一條數(shù)據(jù) 學(xué)科拼接成一條
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 '學(xué)科'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 關(guān)聯(lián)變量條件修改
新聞熱點
疑難解答
圖片精選
