欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-24 12:22:25
前言 mysql的语法相信对大家来说都不是难事,但是本文主要给分享了一些mysql复杂用法的相关内容,通过这篇文章相信大家会对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

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

子查询、查询临时表、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

总结

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