数据中可能存在层次关系,本文章主要介绍查询这种关系的实例。会大量使用递归式 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;