mysql中的一些稍微复杂用法实例代码

前言

mysql的语法相信对大家来说都不是难事，但是本文主要给分享了一些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

子查询、查询临时表、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 = ‘2022’

AND o.update_time >= ‘2022-08-14 20:28:55’

AND o.update_time <= ‘2022-08-14 20:28:55’

) AS a

WHERE

1 = 1

AND a.deal_user_id = 145316

AND a.first_time >= ‘2022-08-17 00:00:00’

AND a.first_time <= ‘2022-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 > ‘2022-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
总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对共生网络的支持。