mysql中的一些稍微复杂用法实例代码
发布网友
发布时间：2022-04-21 05:39
共1个回答
热心网友
时间：2022-04-07 17:31
mysql的语法相信对大家来说都不是难事，但是本文主要给分享了一些mysql复杂用法的相关内容，通过这篇文章相信大家会对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-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
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。
您可能感兴趣的文章:MySQL在关联复杂情况下所能做出的一些优化Mysql一些复杂的sql语句(查询与删除重复的行)深入mysql
"ON
DUPLICATE
KEY
UPDATE"
语法的分析MySQL
最基本的SQL语法/语句MySQL与Oracle的语法区别详细对比浅析Mysql
Join语法以及性能优化MySQL
ALTER语法的运用方法MySQL
prepare语句的SQL语法MySQL进阶SELECT语法篇MySQL
SQL
语法参考
