ORACLE樹查詢學習

昨天CSDN裡處理問題的時候，發現了一個文法connect_by_root，眼前一亮，好像沒有見過，經常用的隻是connect by ，于是翻開文檔重新學習了下

先看自帶的SCHEMA的SCOTT的資料：

SQL>SELECT e.empno,e.ename,e.mgr,e.deptno  FROM emp e;

EMPNO ENAME     MGR DEPTNO

7369 SMITH 7902 20

7499 ALLEN 7698 30

7521 WARD 7698 30

7566 JONES 7839 20

7654 MARTIN 7698 30

7698 BLAKE 7839 30

7782 CLARK 7839 10

7788 SCOTT 7566 20

7839 KING  10

7844 TURNER 7698 30

7876 ADAMS 7788 20

7900 JAMES 7698 30

7902 FORD 7566 20

7934 MILLER 7782 10

階層查詢溫習

■PRIOR

階層查詢的CONNECY BY condition的條件式需要用到PRIOR來指定父節點，

作為運算符，PRIOR和加(+)減(-)運算的優先級相同。 這個很重要，重點了解下：

prior empno= mgr

意思是：祖先（上一層記錄）的empno等于本條記錄的mgr，即：通過根節點周遊子節點

■階層查詢

文法：START WITH condition CONNECT BY NOCYCLE condition

START WITH 指定階層的根

CONNECT BY 指定階層的父/子關系

NOCYCLE 存在CONNECT BY LOOP的紀錄時，也傳回查詢結果。

condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr

■CONNECT BY子句的例子

通過CONNECT BY子句定義職員和上司的關系。

■LEVEL的例子

通過LEVEL虛拟清單示節點的關系。

從上級往下級夠造樹查詢（預設排序）：

這個公司的老大是KING，

7839 KING  10

因為他沒有上級，按照EMPNO和MGR排下層級關系：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL

  FROM EMP E

 START WITH E.EMPNO = 7839

CONNECT BY PRIOR E.EMPNO = E.MGR;

EMPNO ENAME   MGR   DEPTNO   LEVEL

7839 KING  10 1

7566 JONES 7839 20 2

7788 SCOTT 7566 20 3

7876 ADAMS 7788 20 4

7902 FORD 7566 20 3

7369 SMITH 7902 20 4

7698 BLAKE 7839 30 2

7499 ALLEN 7698 30 3

7521 WARD 7698 30 3

7654 MARTIN 7698 30 3

7844 TURNER 7698 30 3

7900 JAMES 7698 30 3

7782 CLARK 7839 10 2

7934 MILLER 7782 10 3

可以很清晰的看到層級關系，同時也可以看到結果按照DEPTNO和LEVEL的排序，看到部門的組成層級關系，如果我這樣得到組織機構了，我還想按人名排序呢，因為預設的排序是按照EMPNO來進行的，此時：

■START WITH子句的排序

通過START WITH指定根節點，ORDER SIBLINGS BY保持階層的順序。

從上級往下級夠造樹查詢（按組織的人名排序）：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL

  FROM EMP E

 START WITH E.EMPNO = 7839

CONNECT BY PRIOR E.EMPNO = E.MGR

 ORDER SIBLINGS BY E.ENAME;

EMPNO ENAME   MGR   DEPTNO   LEVEL

7839 KING  10 1

7698 BLAKE 7839 30 2

7499 ALLEN 7698 30 3

7900 JAMES 7698 30 3

7654 MARTIN 7698 30 3

7844 TURNER 7698 30 3

7521 WARD 7698 30 3

7782 CLARK 7839 10 2

7934 MILLER 7782 10 3

7566 JONES 7839 20 2

7902 FORD 7566 20 3

7369 SMITH 7902 20 4

7788 SCOTT 7566 20 3

7876 ADAMS 7788 20 4

從下往上查詢組織關系，例如我想知道

7369 SMITH 7902 20 4 他上面有幾個上司，哪個是直屬上司呢（會幹活的人，上司一般就是多，我是其中之一，杯具，不過發展空間多，雖然累,自我激勵下），這個時候如何查呢：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL

  FROM EMP E

 START WITH E.EMPNO = 7369

CONNECT BY PRIOR E.MGR = E.EMPNO

 ORDER SIBLINGS BY E.ENAME;

EMPNO ENAME MGR DEPTNO LEVEL

7369 SMITH 7902 20 1

7902 FORD 7566 20 2

7566 JONES 7839 20 3

7839 KING  10 4

上司的個數和我差不多。。哈哈，還有一種情況，我們公司這種家族企業，雖然再人事組織關系上，上司關系鮮明，但是，例如SIMITH是最大上司KING的老婆，那麼實際上的關系可能是這樣的，SIMIT雖然聽從FORD的工作安排指令，但是确可以直接安排老總KING，那麼我将家族這種特殊的關系引入到組織機構裡會怎樣呢，請看：

先做下更新，将KING的上司MGR更新為SMITH的EMPNO：

UPDATE emp SET mgr = 7369 WHERE empno = 7839;

這個時候再去查詢SMITH有多少個老大的時候就會無限制循環了，看看結果如何：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL

  FROM EMP E

 START WITH E.EMPNO = 7369

CONNECT BY PRIOR E.MGR = E.EMPNO

 ORDER SIBLINGS BY E.ENAME

ORA-01436: CONNECT BY loop in user data

報錯了原因是因為産生了CONNECT BY LOOP，這個時候怎麼辦呢，取組織關系的時候加上 NOCYCLE，試試就知道了：

SQL>SELECT E.EMPNO, E.ENAME, E.MGR, E.DEPTNO, LEVEL

  FROM EMP E

 START WITH E.EMPNO = 7369

CONNECT BY NOCYCLE PRIOR E.MGR = E.EMPNO

 ORDER SIBLINGS BY E.ENAME;

得到了正确的結果，開來家族企業也得公私分開啊，不然隻能停滞不前啊。

例如我們想看二把手JONES在公司的地位，關鍵看他上司了幾個部門，最重要的是什麼部門。例如财務部是生殺大權的誕生地，采購部是油水回流地，查檢視：

SQL>

SELECT ENAME "Employee",

       CONNECT_BY_ISCYCLE "Cycle",

       EMPNO,

       MGR,

       LEVEL,

       SYS_CONNECT_BY_PATH(ENAME, '/') "Path"

  FROM EMP

 WHERE LEVEL <= 5

   AND DEPTNO = 20

 START WITH ENAME = 'JONES'

CONNECT BY NOCYCLE PRIOR EMPNO = MGR

       AND LEVEL <= 5;

Employee Cycle EMPNO MGR LEVEL Path

JONES 0 7566 7839 1 /JONES

SCOTT 0 7788 7566 2 /JONES/SCOTT

ADAMS 0 7876 7788 3 /JONES/SCOTT/ADAMS

FORD 0 7902 7566 2 /JONES/FORD

SMITH 0 7369 7902 3 /JONES/FORD/SMITH

底下一共有4名員工，員工的上司關系一目了然。

如果我想知道這個公司每個部門每個月要發多少工資，首先明确10部門是KING所在的部門，這個部門相當于管理部門，負責管理底下所有的部門，這個時候怎麼辦？好的，看看下面的：

SQL>SELECT NAME, SUM(SAL) AS SAL

  FROM (SELECT CONNECT_BY_ROOT ENAME AS NAME, ENAME, LEVEL, SAL

          FROM EMP

         WHERE DEPTNO = 10

        CONNECT BY NOCYCLE PRIOR EMPNO = MGR)

 GROUP BY NAME;

NAME            SAL

MILLER         1300

CLARK          3750

KING              8750

OK,溫習完畢，再來解答文章裡的這個問題

http://topic.csdn.net/u/20110928/11/1306858a-ba5d-4d71-b7c2-984bc3fd20f7.html

大緻轉換到SCOTT表裡的需求為：查2把手的工資總和 或者說給定條件，查詢下一層級的工資總和

這裡是老總KING要給他的直接下屬總共發多少工資？如下SQL：

SQL>SELECT EMPNO, SUM(SAL) AS TOTAL_SAL

  FROM (SELECT CONNECT_BY_ROOT E.EMPNO AS EMPNO,

               E.ENAME,

               E.SAL,

               LEVEL AS ILEVEL

          FROM EMP E

         WHERE LEVEL = 2

         START WITH E.EMPNO = 7839

        CONNECT BY PRIOR E.EMPNO = E.MGR)

 GROUP BY EMPNO;

EMPNO                TOTAL_SAL

7839                       8275

 或許應該再算下獎金，或者是年終獎，不過想想沒有必要，這麼拼命的做項目，數量再大也不會過超過4位數，花了幾個小時學習下不足的知識點。。。以慰藉内心！