数据库语法总结（13）——分层级查询

我们在这章主要讲述一下层级查询，什么是层级查询呢，简单来说就是父子级关系

1、父级——子级在同一个结果集中展示

现在有一张职位表tb_position，其中有员工号empno，员工姓名empname，所属上级mgr信息（如果没有上级，则为null）：

empno	empname	mgr
001	张三	null
002	李四	001
003	王五	001
004	赵六	002
005	孙七	004

现在我们想展现这种形式：李四为张三工作，赵六为李四工作……

很简单，就是做内联查询，只是各种数据库连接字符串的方式不同

--DB2、Oracle、PostgreSql   连接符为||
select a.empname || '为' || b.empname as work
from tb_position a,tb_position b
where a.mgr=b.empno

--Mysql        连接符为 函数concat
--SqlServer    连接符为 +

2、子级——父级——祖父级展现在同一个结果集中

还是上面的表tb_position，想要的效果为赵六——>李四——>张三；孙七——>赵六——>李四…

这个和上面的的比多了一个层级，三个层级，我们知道在这个表里其实包含了四个层级，那我们怎么做到取三个层级展示呢，在Oracle中提供了函数sys_connect_by_path，其他数据库没有这些函数可以用cte中的with方式

--Oracle方式
select ltrim(sys_connect_by_path(empname,'——>'),'——>') work
    from tb_position
where level=3
connect by prior mgr=empno

--DB、SqlServer    MySQL、PostgreSql和前面两种数据库语法上略有不同，会在下面标注
with tb_level (work,mgr,levels)  --MySQL、PostgreSql需要在with后面加recursive
as(
select cast(empname as varchar(100)),
       mgr,0
    from tb_position
union all
select cast(tb_level.work +'——>' + p.empname as varchar(100)),
--MySQL、PostgreSql    cast(concat(tb_level.work,'——>',p.empname) as varchar(100)),
       e.mgr,tb_level.levels +1
    from tb_position p,tb_level
where tb_level.mgr = p.empno
)

select work 
    from tb_level 
where levels=2 

扩展：关于Oracle中的ltrim()函数和sys_connect_by_path函数我们之前第6章第7节讲过一些数据库语法总结（6）——处理字符串_数据库字符串操作-CSDN博客

此时的sys_connect_by_path的函数可以返回每个层级的empname，然后用level限制层级为3就可以了

3、所有层级关系的结果集展示

我们知道上述tb_position中包含很多不同层级关系，我们现在想要展示所有的层级关系到同一个结果集中，想要的效果如图所示

work

张三

张三--李四

张三--李四--赵六

张三--李四--赵六--孙七

张三--王五

和第2节的的实现逻辑类似只是不再限制层级，我们在这列举一下DB2和Oracle的写法，其他数据库参考DB2，修改一下拼接连接符一些注意的的点即可

--Oracle
select ltrim(sys_connect_by_path(empname,'--'),'--') work
from tb_position
    start with mgr is null
connect by prior empno=mgr
order by 1

--DB2
with tb_levels(empname,empno)
as(
    select cast(empname as varchar(100)),empno
    from tb_position
    where mgr is null
        union all
    select cast(l.empname || '--' || p.empname as varchar(100),
           p.empno
    from tb_position p,tb_levels l
    where p.mgr=l.mgr
)
select empname as work
from tb_levels
order by 1

4、找出给定父级的所有子级

例如我们想要找出 张三 及其下属员工(包括直接下属和间接下属)：李四、王五、赵六、孙七

--Oracle
select empname
from tb_position
    start with empname='张三'
connect by prior empno=mgr

--DB2、PosrgreSql、SqlServer、Mysql
with tb_level(empname,empno)    --Mysql在with后加recursive
as (
    select empname,empno
    from tb_position
    where empname='张三'
        union all
    select p.empname,p.empno
    from tb_position p,tb_level l
    where l.empno=p.mgr
)
select empname
from tb_level

5、确定叶子节点、分支节点和根节点

首先我们先说明一下标题的含义，叶子节点(is_leaf)：没有子级；分支节点(is_banch)：既是子级又是父级；根节点(is_root)：只有子级，没有父级。返回1(true)或0(false)

empname	is_leaf	is_banch	is_root
张三	0	0	1
李四	0	1	0
王五	0	1	0
赵六	1	0	0
孙七	1	0	0

 Oracle中提供了内置函数connect_by_isleaf和connect_by_root两个内置函数来查找叶子节点和根节点

select empname,
       connect_by_leaf is_leaf,
       (select count(*) from tb_position p
        where p.mgr=tb_position.empno
            and tb_position.mgr is not null
            and rownum=1)  is_banch,
       decode(empname.connect_by_root(empname),1,0)  is_root
    from tb_position
start with mgr is null
connect by prior empno=mgr
order by 4 desc,3desc

DB2、PosrgreSql、SqlServer、Mysql需要进行层级处理，使用3个标量子查询

select p.empname,
       (select sign(count(*)) from tb_position a
            where 0=
                    (select count(*) from tb_position b
                        where b.mgr=p.empno)
        ) as is_leaf,
       (select sign(count(*)) from tb_position a
            where a.mgr=p.empno
              and p.mgr is not null
        ) as is_branch,
       (select sign(count(*)) from tb_position a
            where a.empno=p.empno
              and a.mgr is null
        ) as is_root
from tb_position p
order by 4 desc,3 desc

好了，分层级查询今天就先到这了，有补充的小伙伴可以私信留言