SQL 基礎8.1——階層化查詢(START BY ... CONNECT BY PRIOR)

 階層化查詢,即樹型結構查詢,是SQL中經常用到的功能之一,通常由根節點,父節點,子節點,葉節點組成,其文法如下:

       SELECT [LEVEL] ,column,expression,...

       FROM table_name

       [WHERE where_clause]

       [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

       LEVEL:為僞列,用于表示樹的層次

       start_condition：階層化查詢的起始條件

       prior_condition：定義父節點和子節點之間的關系

    --使用start with ...connect by prior 從根節點開始周遊

    SQL> select empno,mgr,ename,job from emp

      2  start with empno = 7839

      3  connect by prior empno = mgr;

        EMPNO        MGR ENAME      JOB

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

         7839            KING       PRESIDENT

         7566       7839 JONES      MANAGER

         7788       7566 SCOTT      ANALYST

         7876       7788 ADAMS      CLERK

         7902       7566 FORD       ANALYST

         7369       7902 SMITH      CLERK

         7698       7839 BLAKE      MANAGER

         7499       7698 ALLEN      SALESMAN

         7521       7698 WARD       SALESMAN

         7654       7698 MARTIN     SALESMAN

         7844       7698 TURNER     SALESMAN

         7900       7698 JAMES      CLERK

         7782       7839 CLARK      MANAGER

         7934       7782 MILLER     CLERK

    14 rows selected.

    樹型結構周遊過程(通過上面的查詢來描述)

       1).從根節點開始(即where_clause中的條件,如果為非根節點則分根節點作為根節點開始周遊,如上例empno = 7839)

       2).周遊根節點(得到empno = 7839記錄的相關資訊)

       3).判斷該節點是否存在有子節點，如果有，則通路最左側未被通路的子節點,轉到1，否則下一步

           如上例中prior_condition為empno = mgr（下一條查詢記錄傳回的mgr值等于前一條記錄的empno）,即子節點的mgr等于父節點的empno,在此時下一條的mgr為7839的記錄

       4).當節點為葉節點，則通路完畢，進入下一步，否則,轉到3。

       5).傳回到該節點的父節點,轉到3)

    --僞列level的使用

    --注意connect by prior empno = mgr 的了解

    --prior表示前一條記錄,即下一條傳回記錄的mgr應當等于前一條記錄的empno

    SQL> select level,empno,mgr,ename,job from emp

      2  start with ename = 'KING'

      3  connect by prior empno = mgr

      4  order by level;

        LEVEL      EMPNO        MGR ENAME      JOB

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

            1       7839            KING       PRESIDENT

            2       7566       7839 JONES      MANAGER

            2       7698       7839 BLAKE      MANAGER

            2       7782       7839 CLARK      MANAGER

            3       7902       7566 FORD       ANALYST

            3       7521       7698 WARD       SALESMAN

            3       7900       7698 JAMES      CLERK

            3       7934       7782 MILLER     CLERK

            3       7499       7698 ALLEN      SALESMAN

            3       7788       7566 SCOTT      ANALYST

            3       7654       7698 MARTIN     SALESMAN

            3       7844       7698 TURNER     SALESMAN

            4       7876       7788 ADAMS      CLERK

            4       7369       7902 SMITH      CLERK

    --獲得層次數

    SQL> select count(distinct level) "Level" from emp

        Level

    ----------

            4 

    --格式化層次查詢結果(使用左填充* level - 1個空格)

    SQL> col Ename for a30

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename",

      3    job

      4  from emp

      5  start with ename = 'KING'

      6  connect by prior empno = mgr;

        LEVEL Ename                          JOB

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

            1  KING                          PRESIDENT

            2    JONES                       MANAGER

            3      SCOTT                     ANALYST

            4        ADAMS                   CLERK

            3      FORD                      ANALYST

            4        SMITH                   CLERK

            2    BLAKE                       MANAGER

            3      ALLEN                     SALESMAN

            3      WARD                      SALESMAN

            3      MARTIN                    SALESMAN

            3      TURNER                    SALESMAN

            3      JAMES                     CLERK

            2    CLARK                       MANAGER

            3      MILLER                    CLERK

    --從非根節點開始周遊(隻需修改start with 中的條件即可)

      5  start with ename = 'SCOTT'

            1  SCOTT                         ANALYST

            2    ADAMS                       CLERK

    --從下向上周遊(交換connect by prior中的條件即可,使用mgr

= empno)

    --注意connect by prior mgr = empno 的了解

    --prior表示前一條記錄,即下一條傳回記錄的empno應當等于前一條記錄的mgr

      6  connect by prior mgr = empno;

            3      KING                      PRESIDENT

    --從下向上周遊(也可以将prior置于等号右邊,得到相同的結果)

      6  connect by empno = prior mgr;

    --從層次查詢中删除節點和分支

      2    lpad(' ',2 * level - 1) || ename as "Ename"

      3    ,job

      5  where ename != 'SCOTT'    --通過where子句來過濾SCOTT使用者，但SCOTT的下屬ADAMS并沒有過濾掉

      6  start with empno = 7839   

      7  connect by prior empno = mgr;

        LEVEL Ename                JOB

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

            1  KING                PRESIDENT

            2    JONES             MANAGER

            4        ADAMS         CLERK

            3      FORD            ANALYST

            4        SMITH         CLERK

            2    BLAKE             MANAGER

            3      ALLEN           SALESMAN

            3      WARD            SALESMAN

            3      MARTIN          SALESMAN

            3      TURNER          SALESMAN

            3      JAMES           CLERK

            2    CLARK             MANAGER

            3      MILLER          CLERK

    13 rows selected.

    --通過将過濾條件由where 子句的内容移動到connect by prior 子句中過濾掉SCOTT及其下屬

      5  start with empno = 7839

      6  connect by prior empno = mgr and ename != 'SCOTT';

    12 rows selected.

    --在階層化查詢中增加過濾條件或使用子查詢

      5  where sal > 2500

      6  start with empno = 7839

      7  connect by prior empno = mgr                     

      8  ;

            3      SCOTT           ANALYST

      5  where sal > (select avg(sal) from emp)

      7  connect by prior empno = mgr ;

    6 rows selected.