层次结构查询

来源：互联网发布：梁朝伟刘嘉玲知乎编辑：程序博客网时间：2024/06/06 14:07

利用SCOTT.EMP中的empno和mgr的关系做个示例：

使用层次结构查询，一个语句就能查出（EMPNO和MGR关系的）整个树结构

col level for a15
col ename for a10
set pagesize 100

1.语句一

SELECT EMPNO,ENAME,MGR,level
FROM EMP
CONNECT BY prior EMPNO=MGR
START WITH ENAME='KING'
/

     EMPNO ENAME                       MGR      LEVEL
---------- -------------------- ---------- ----------
      7839 KING                                     1
      7566 JONES                      7839          2
      7788 SCOTT                      7566          3
      7876 ADAMS                      7788          4
      7902 FORD                       7566          3
      7369 SMITH                      7902          4
      7698 BLAKE                      7839          2
      7499 ALLEN                      7698          3
      7521 WARD                       7698          3
      7654 MARTIN                     7698          3
      7844 TURNER                     7698          3
      7900 JAMES                      7698          3
      7782 CLARK                      7839          2
      7934 MILLER                     7782          3

注：这个结构是从左到右的顺序排序

2.语句二

SELECT EMPNO,ENAME,MGR,lpad('|',level*3) "LEVEL"
FROM EMP
CONNECT BY prior EMPNO=MGR
START WITH ENAME='KING'
/

     EMPNO ENAME                       MGR LEVEL
---------- -------------------- ---------- --------------------
      7839 KING                              |
      7566 JONES                      7839      |
      7788 SCOTT                      7566         |
      7876 ADAMS                      7788            |
      7902 FORD                       7566         |
      7369 SMITH                      7902            |
      7698 BLAKE                      7839      |
      7499 ALLEN                      7698         |
      7521 WARD                       7698         |
      7654 MARTIN                     7698         |
      7844 TURNER                     7698         |
      7900 JAMES                      7698         |
      7782 CLARK                      7839      |
      7934 MILLER                     7782         |

3.语句三

SELECT EMPNO,ENAME,MGR,lpad('*',level,'*') "LEVEL"
FROM EMP
CONNECT BY prior EMPNO=MGR
START WITH ENAME='KING'
/

     EMPNO ENAME                       MGR LEVEL
---------- -------------------- ---------- --------------------
      7839 KING                            *
      7566 JONES                      7839 **
      7788 SCOTT                      7566 ***
      7876 ADAMS                      7788 ****
      7902 FORD                       7566 ***
      7369 SMITH                      7902 ****
      7698 BLAKE                      7839 **
      7499 ALLEN                      7698 ***
      7521 WARD                       7698 ***
      7654 MARTIN                     7698 ***
      7844 TURNER                     7698 ***
      7900 JAMES                      7698 ***
      7782 CLARK                      7839 **
      7934 MILLER                     7782 ***

4.语句四

除去其中某个节点不显示，如不显示scott单个人

SELECT EMPNO,ENAME,MGR,lpad('|',level*3) "LEVEL"
FROM EMP
where ename!='SCOTT'
CONNECT BY prior EMPNO=MGR
START WITH ENAME='KING'
/

5.语句五

不显示单个节点及节点以后的分支，以scott为例

SELECT EMPNO,ENAME,MGR,lpad('|',level*3) "LEVEL"
FROM EMP
CONNECT BY prior EMPNO=MGR
and ename!='SCOTT'
START WITH ENAME='KING'
/