Oracle 技能强化 Part 12 分层查询

1. 树形查询简介

select empno as 员工编码,
       PRIOR empno as 上一级别员工编码,
       mgr as 主管编码,
       ename as 姓名,
       /*LEVEL:伪劣，返回当前行所在级别*/
       LEVEL AS 级别,
       /*级别为1的就是根结点*/
       decode(LEVEL, 1, 1, 0) AS 根结点,
       /*connect_by_isleaf是伪劣，表示当前节点为叶子节点*/
       connect_by_isleaf as 叶子节点,
       case
         when connect_by_isleaf = 1 Then
          0
         when LEVEL = 1 THEN
          0
         ELSE
          1
       END AS 分支节点,
       sys_connect_by_path(empno, ',') as empno_path
  FROM emp
/*由上级主管为空的行开始递归查询*/
 START WITH mgr is null
/*递归条件，主管编码就是上一级的员工编码*/
CONNECT BY PRIOR empno = mgr
/*对同一个主管如7698下面的员工排序，不能直接用ORDER BY,需要如下所示加SIBLINGS关键字*/
 ORDER SIBLINGS BY empno DESC;

等价写法如下：

WITH lv1 AS
 (SELECT 1 AS 级别,
         to_char(row_number() over(ORDER BY empno)) AS sn,
         ',' || b.empno AS empno_path,
         b.*
    FROM emp b
   WHERE b.mgr IS NULL),
lv2 AS
 (SELECT a.级别 + 1 AS 级别,
         a.sn || to_char(row_number() over(ORDER BY b.empno)) AS sn,
         a.empno_path || ',' || b.empno AS empno_path,
         b.*
    FROM lv1 a
   INNER JOIN emp b
      ON b.mgr = a.empno),
lv3 AS
 (SELECT a.级别 + 1 AS 级别,
         a.sn || to_char(row_number() over(ORDER BY b.empno)) AS sn,
         a.empno_path || ',' || b.empno AS empno_path,
         b.*
    FROM lv2 a
   INNER JOIN emp b
      ON b.mgr = a.empno),
lv4 AS
 (SELECT a.级别 + 1 AS 级别,
         a.sn || to_char(row_number() over(ORDER BY b.empno)) AS sn,
         a.empno_path || ',' || b.empno AS empno_path,
         b.*
    FROM lv3 a
   INNER JOIN emp b
      ON b.mgr = a.empno)
SELECT empno AS 员工编码, mgr AS 主管编码, ename AS 姓名, 级别, empno_path
  FROM (SELECT *
          FROM lv1
        UNION ALL
        SELECT *
          FROM lv2
        UNION ALL
        SELECT *
          FROM lv3
        UNION ALL
        SELECT *
          FROM lv4)
 ORDER BY sn;
 

2.树形查询中的WHERE

限定对部门20的人员进行树形查询，以下结果不对哦。

select empno  as employee_code,
       mgr    as director_code,
       ename  as names,
       deptno as department_code
  from emp
 where deptno = 20
 start with mgr is null
connect by prior empno = mgr;

上图显示的结果是不对的，因为部门 20 不存在 mgr 为空的数据，那么也就不该返数据。正确的查询语句如下：

select empno  as employee_code,
       mgr    as director_code,
       ename  as names,
       deptno as department_code
  from (select * from emp where deptno = 20) emp
 start with mgr is null
connect by prior empno = mgr;

谨记，涉及到有条件限制的树形查询，用子查询进行过滤即可。

 3.查询树形的一个分支

查询树形的一个分支，不能用where,用START WITH 指定分支的起点就可以了。如我们查询员工编号为7698及下级所有员工。

SELECT empno as employee_code,
       mgr   as director_code,
       ename as names,
       level as class_level
  FROM emp
 START WITH empno = 7698
CONNECT BY PRIOR empno = mgr;

 4. 剪去一个分支

要求剪去7698，不能在where条件中加条件进行剪去，由于树形查询递归是根据prior empno = mgr 进行的，所以直接加条件剪去就好。

SELECT empno as employee_code,
       mgr   as director_code,
       ename as names,
       level as class_level
  FROM emp
 START WITH mgr is null
CONNECT BY PRIOR empno = mgr
       and empno != 7698;/*剪去分支*/