mysql 8.0“奇技淫巧” 之递归查询和行转列实践

原创
2022/06/25 15:43
阅读数 772

接到一个需求,sql语句查询某人所在部门所有上级人员,用程序实现不用这么麻烦,但要求用sql实现,有点烧脑,网上一查发现mysql 8.0有递归查询功能。 思路:

  1. 先查到某人所在部门
  2. 递归查询该部门所有上级部门和对应负责人
  3. 数据行转列

简化后相关表:

sys_depart (部门表):

id depart_name parent_id org_code
部门id 部门名称 父级部门id 部门code

sys_user(用户表):

id username user_identity depart_ids
id 姓名 身份1普通成员 2上级 负责部门逗号分隔

sys_user_depart(用户部门表)

id user_id dep_id
id 用户id 部门id

递归查询要求数据库版本:mysql 8.0

一、先来个简单的,例如要查询某个部门的所有父部门sql:

WITH RECURSIVE cte AS (
    SELECT sd.id, sd.depart_name, sd.parent_id, sd.org_code
	FROM sys_depart sd WHERE id ='1fc469df521d47f3a24c2dbf9af1845e'
    UNION ALL
    SELECT sd.id, sd.depart_name, sd.parent_id, sd.org_code
	FROM sys_depart sd,  cte  WHERE sd.id = cte.parent_id
)
SELECT * FROM cte

二、修改一下,递归时每上升一级+1

WITH RECURSIVE cte AS (
    SELECT 1 as depart_level,sd.id, sd.depart_name, sd.parent_id, sd.org_code FROM sys_depart sd WHERE id ='1fc469df521d47f3a24c2dbf9af1845e'
    UNION ALL
    SELECT (1 + cte.depart_level) as depart_level ,sd.id, sd.depart_name, sd.parent_id, sd.org_code FROM sys_depart sd, cte WHERE sd.id = cte.parent_id   
)
SELECT * FROM cte

三、继续改造:查询对应部门负责人

WITH RECURSIVE cte AS (
    select 1 as depart_level, sud.user_id as gid,sud.user_id,su.realname, sd.id, sd.depart_name, sd.parent_id, sd.org_code from sys_depart as sd join sys_user_depart as sud on sd.id = sud.dep_id
    join sys_user as su on sud.user_id = su.id where  su.work_status !=2 
     and sud.user_id in('1467788797084180482')
    UNION ALL
    SELECT (1 + cte.depart_level) as depart_level ,cte.gid,a.user_id,a.realname, a.id, a.depart_name, a.parent_id, a.org_code FROM 
    (
        select sud.user_id, su.realname,su.depart_ids, su.`user_identity`, sd.id, sd.depart_name, sd.parent_id, sd.org_code from sys_depart as sd join sys_user_depart as sud on sd.id = sud.dep_id
        join sys_user as su on sud.user_id = su.id 
    ) a, cte WHERE
      if(depart_level = 1, FIND_IN_SET(cte.id, a.depart_ids), FIND_IN_SET(cte.parent_id, a.depart_ids))  and a.`user_identity` = 2
)
select * from cte

四、最后把行数据转列数据

WITH RECURSIVE cte AS (
    select 1 as depart_level, sud.user_id as gid,sud.user_id,su.realname, sd.id, sd.depart_name, sd.parent_id, sd.org_code from sys_depart as sd join sys_user_depart as sud on sd.id = sud.dep_id
    join sys_user as su on sud.user_id = su.id where  su.work_status !=2 
     and sud.user_id in('1467788797084180482')
    UNION ALL
    SELECT (1 + cte.depart_level) as depart_level ,cte.gid,a.user_id,a.realname, a.id, a.depart_name, a.parent_id, a.org_code FROM 
    (
        select sud.user_id, su.realname,su.depart_ids, su.`user_identity`, sd.id, sd.depart_name, sd.parent_id, sd.org_code from sys_depart as sd join sys_user_depart as sud on sd.id = sud.dep_id
        join sys_user as su on sud.user_id = su.id 
    ) a, cte WHERE
      if(depart_level = 1, FIND_IN_SET(cte.id, a.depart_ids), FIND_IN_SET(cte.parent_id, a.depart_ids))  and a.`user_identity` = 2
)
select gid as uid,
max(case depart_level WHEN 1 THEN realname end) as '姓名',
max(case depart_level WHEN 2 THEN realname end) as 'leader1',
max(case depart_level WHEN 3 THEN realname end) as 'leader2',
max(case depart_level WHEN 4 THEN realname end) as 'leader3'
from cte
group by gid

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部