层次查询

在scott用户下查询emp表

SQL> select empno, ename, mgr from emp;

     EMPNO ENAME                       MGR

---------- -------------------- ----------

      7499 ALLEN                      7698

      7521 WARD                       7698

      7654 MARTIN                     7698

      7698 BLAKE                      7839

      7782 CLARK                      7839

      7839 KING

      7844 TURNER                     7698

      7900 JAMES                      7698

      7934 MILLER                     7782

可以看出empno为7839的MGR为空，他没有上级

下面用层次查询

SQL> select empno, ename, mgr, level

  2  from emp

  3  start with empno = 7839   --该员工为根节点

  4  connect by prior empno = mgr;

     EMPNO ENAME                       MGR      LEVEL

---------- -------------------- ---------- ----------

      7839 KING                                     1

      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

已选择9行。

CONNECT by字句说明每行数据按层次查询，PRIOR在的一侧表示父节点，另一侧为子节点。

START WITH表示哪个节点为根节点。如果不指定哪个是根节点那么每个节点都为根节点。

START WITH可以接子查询

  1  select empno, ename, mgr, level

  2  from emp

  3  start with empno = (select empno from emp where mgr is null)

  4* connect by prior empno = mgr

SQL> /

     EMPNO ENAME                       MGR      LEVEL

---------- -------------------- ---------- ----------

      7839 KING                                     1

      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

已选择9行。

可以通过分解子查询查看某一个等级

  1  WITH temp as

  2  (select a.empno, a.ename, a.mgr, level lev from emp a

  3  start with empno = 7839

  4  connect by prior a.empno = a.mgr)

  5  select * from temp

  6  where lev =

  7* (select lev from temp where empno = 7499)

SQL> /

     EMPNO ENAME                       MGR        LEV

---------- -------------------- ---------- ----------

      7499 ALLEN                      7698          3

      7521 WARD                       7698          3

      7654 MARTIN                     7698          3

      7844 TURNER                     7698          3

      7900 JAMES                      7698          3

      7934 MILLER                     7782          3

已选择6行。

结合RPAD填充空格

  1  select empno, RPAD(' ',level*3)||ename name, mgr

  2  from emp

  3  start with empno = 7839

  4* connect by prior empno = mgr

SQL> /

     EMPNO NAME                                  MGR

---------- ------------------------------ ----------

      7839    KING

      7698       BLAKE                          7839

      7499          ALLEN                       7698

      7521          WARD                        7698

      7654          MARTIN                      7698

      7844          TURNER                      7698

      7900          JAMES                       7698

      7782       CLARK                          7839

      7934          MILLER                      7782

已选择9行。

Where条件是在层次结果查出来之后进行的过滤， 所以还是遵守原有的层级关系

  1  select empno, RPAD(' ',level*3)||ename name, mgr, deptno

  2  from emp

  3  where deptno = 30

  4  start with empno = 7839

  5* connect by prior empno = mgr

SQL> /

     EMPNO NAME                        MGR     DEPTNO

---------- -------------------- ---------- ----------

      7698       BLAKE                7839         30

      7499          ALLEN             7698         30

      7521          WARD              7698         30

      7654          MARTIN            7698         30

      7844          TURNER            7698         30

      7900          JAMES             7698         30

sys_connect_by_path函数可以将父节点到当前节点的路径显示出来

  1  select empno, rpad(' ',level*3)||ename haha, mgr,sys_connect_by_path(ename'->') aa

  2  from emp

  3  start with empno = 7839

  4* connect by prior empno = mgr

 

     EMPNO HAHA                                  MGR AA

---------- ------------------------------ ---------- --------------------

      7839    KING                                   ->KING

      7698       BLAKE                          7839 ->KING->BLAKE

      7499          ALLEN                       7698 ->KING->BLAKE->ALLEN

      7521          WARD                        7698 ->KING->BLAKE->WARD

      7654          MARTIN                      7698 ->KING->BLAKE->MARTIN

                                                     

      7844          TURNER                      7698 ->KING->BLAKE->TURNER

                                                     

      7900          JAMES                       7698 ->KING->BLAKE->JAMES

     EMPNO HAHA                                  MGR AA

---------- ------------------------------ ---------- --------------------

      7782       CLARK                          7839 ->KING->CLARK

      7934          MILLER                      7782 ->KING->CLARK->MILLER

                                                     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25361369/viewspace-713291/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25361369/viewspace-713291/