前言
mysql的語法相信對大家來說都不是難事，但是本文主要給分享了一些mysql復(fù)雜用法的相關(guān)內(nèi)容，通過這篇文章相信大家會(huì)對mysql更深的了解一些，下面話不多說了，來一起看看詳細(xì)的介紹吧
一對多數(shù)據(jù)顯示成一行
GROUP_CONCAT(expr)
1、涉及的表關(guān)系:teacher表、teacher_subject_rel表(教師所能教的學(xué)科表)、subject表
2、業(yè)務(wù)場景: 需要拉取所有教師的編號(teacher_no)、學(xué)科名(subject_name)。 nbsp 教師表(teacher)和學(xué)科(teacher_subject_rel)是一對多關(guān)系, 往往查詢出現(xiàn)的是同一教師多條 數(shù)據(jù)。我們希望得到每個(gè)教師一條數(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
子查詢、查詢臨時(shí)表、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 關(guān)聯(lián)變量條件修改
1、涉及的表關(guān)系: user_info表中的 id_number(身份證號) teacher表中的birth字段、 關(guān)聯(lián)關(guān)系usrer_id = teacher_id
2、業(yè)務(wù)場景：獲取用戶身份證上的出生日期將出生日期更新在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
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化
- Mysql一些復(fù)雜的sql語句(查詢與刪除重復(fù)的行)
- 深入mysql "ON DUPLICATE KEY UPDATE" 語法的分析
- MySQL 最基本的SQL語法/語句
- MySQL與Oracle的語法區(qū)別詳細(xì)對比
- 淺析Mysql Join語法以及性能優(yōu)化
- MySQL ALTER語法的運(yùn)用方法
- MySQL prepare語句的SQL語法
- MySQL進(jìn)階SELECT語法篇
- MySQL SQL 語法參考
