SQL分层查询

时间:2022-10-29 11:10:44

  数据中可能存在层次关系,本文章主要介绍查询这种关系的实例。会大量使用递归式 CTE

   Emps 表中 EName 员工和 MGR 上级之间的关系如下:

  SQL分层查询

 

   每个上级也同样是员工,主管和员工之间为父子关系。

 

1.呈现父子关系

  情景:返回员工数据的同时返回上级信息(名字,角色,薪水)。如下:

  SQL分层查询

 

   解决方案:基于 MGR 和 EName 相等自连接,找出每个员工上级的数据即可解决。

SELECT a.EName 员工名,a.MGR 上级,b.SAL 上级薪水,B.Role 上级角色 FROM test.emps a
left join test.emps b on a.MGR = b.EName

  也可以使用标量子查询,注意标量子查询只能返回一列。

 select a.EName 员工名, 
 (select b.EName FROM test.emps b where b.EName = a.MGR) 上级,
  (select b.SAL FROM test.emps b where b.EName = a.MGR) 上级薪水,
   (select b.Role FROM test.emps b where b.EName = a.MGR) 上级角色
 FROM test.emps a;

 

2.呈现子-父-祖父关系

  情景:员工A的上级是员工M,员工M的上级是员工B,员工B的上级是员工Z。想要呈现出这种关系。如下图:

  SQL分层查询

 

  解决方案:由于要呈现的关系包含多层关系,而且是动态的,所以可以使用CTE递归来实现。

 with RECURSIVE empss as(
    select EName,MGR,ENAme as Re from  test.emps
    union all 
    select a.EName as EName, b.MGR as MGR,CONCAT(a.Re , '->' , b.EName) as Re from empss a
    left join emps b on a.MGR = b.EName
    where a.MGR is not null
 )
 
 select EName 员工,Re 关系 from empss where MGR is null

 

 3.创建基于表的分层视图

  情景:返回一个结果集,将整张表的层次结构呈现出来。在 emps 表中,员工Z上没有上级。从员工Z 开始显示所有下属以及这些下属所有的下属。如下:

  SQL分层查询

 

   解决方案:该结果集相当于上一个情景反过来,区别在于从根开始。同样这里使用 CTE 递归。先找到根,然后通过 EName 和 MGR 连接寻找下属。

with recursive empss as(
    SELECT EName as Re,EName FROM test.emps where MGR is null
    union all
    SELECT concat(b.Re,'-',a.EName) as Re,a.EName FROM test.emps a
    join empss b on a.MGR = b.EName
    
)

select Re 关系 from empss order by Re

 

4.确定叶子节点、分支节点和根节点

  情景:判断给定的行是哪种类型:叶子节点、分支节点还是根节点。在员工表中,叶子节点指的是不是任何员工上级的员工,分支节点指的是自己是上级且还是某个员工的下属,根节点指的是没有上级的员工。如下:

  SQL分层查询

 

   解决方案:使用标量子查询在每行每个节点类型列中返回布尔值。是否叶子节点,通过判断上级是该员工的员工数量,如果是0就表示该员工是叶子节点。是否分支节点,通过判断该员工是否存在上级,并且上级是该员工的员工数量大于0,如果大于0表示该员工是分支节点,这里使用 sign() 函数返回标志。是否根节点,只需要判断该员工是否没有上级即可。

SELECT EName,
    (select  count(*)=0 from  test.emps b where a.EName = b.MGR ) as 是否叶子节点,
    (select sign(count(*)) from  test.emps b where a.EName = b.MGR  and a.MGR is not null) as 是否分支节点,
    a.MGR is null as 是否根节点
 FROM test.emps a;