SQL分层查询 _生活百科

数据中可能存在层次关系，本文章主要介绍查询这种关系的实例。会大量使用递归式 CTE 。
Emps 表中 EName 员工和 MGR 上级之间的关系如下：

文章插图
每个上级也同样是员工，主管和员工之间为父子关系。
1.呈现父子关系
情景：返回员工数据的同时返回上级信息（名字，角色，薪水）。如下：

文章插图
解决方案：基于 MGR 和 EName 相等自连接，找出每个员工上级的数据即可解决。
SELECT a.EName 员工名,a.MGR 上级,b.SAL 上级薪水,B.Role 上级角色 FROM test.emps aleft 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 fromtest.empsunion allselect a.EName as EName, b.MGR as MGR,CONCAT(a.Re , '->' , b.EName) as Re from empss aleft join emps b on a.MGR = b.ENamewhere 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 nullunion allSELECT concat(b.Re,'-',a.EName) as Re,a.EName FROM test.emps ajoin empss b on a.MGR = b.EName)select Re 关系 from empss order by Re4.确定叶子节点、分支节点和根节点
情景：判断给定的行是哪种类型：叶子节点、分支节点还是根节点。在员工表中，叶子节点指的是不是任何员工上级的员工，分支节点指的是自己是上级且还是某个员工的下属，根节点指的是没有上级的员工。如下：

文章插图
解决方案：使用标量子查询在每行每个节点类型列中返回布尔值。是否叶子节点，通过判断上级是该员工的员工数量，如果是0就表示该员工是叶子节点。是否分支节点，通过判断该员工是否存在上级，并且上级是该员工的员工数量大于0，如果大于0表示该员工是分支节点，这里使用 sign() 函数返回标志。是否根节点，只需要判断该员工是否没有上级即可。
SELECT EName,(selectcount(*)=0 fromtest.emps b where a.EName = b.MGR ) as 是否叶子节点,(select sign(count(*)) fromtest.emps b where a.EName = b.MGRand a.MGR is not null) as 是否分支节点,a.MGR is null as 是否根节点 FROM test.emps a;【SQL分层查询】