SQL分层查询

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

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

　　

 

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

 

1.呈现父子关系

　　情景：返回员工数据的同时返回上级信息（名字，角色，薪水）。如下：

　　

 

 　　解决方案：基于 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。想要呈现出这种关系。如下图：

　　

 

　　解决方案：由于要呈现的关系包含多层关系，而且是动态的，所以可以使用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 开始显示所有下属以及这些下属所有的下属。如下：

　　

 

 　　解决方案：该结果集相当于上一个情景反过来，区别在于从根开始。同样这里使用 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.确定叶子节点、分支节点和根节点

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

　　

 

 　　解决方案：使用标量子查询在每行每个节点类型列中返回布尔值。是否叶子节点，通过判断上级是该员工的员工数量，如果是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;