[20150907]樹形查詢.txt

[20150907]樹形查詢.txt

-- 昨天看一條sql語句，嘗試最佳化，發現使用分析函式也不好，好像符合樹形查詢，最後覺得不妥，暫時放棄看看。
-- 但是還是學習樹形查詢的內容。最好的例子是scott.emp表。

-- 我是經常不記語法的人，或者記性不好。我總覺得理解很重要：

--例子：

select * from emp start with mgr is null connect by prior empno = mgr;

--理解： 從 mgr is null , 也就是從最高領導者開始，connect by 條件 相當於理解為前面的empno = 當前的 mgr。
--以上這條相當於從根節點向子節點查詢。
--還有一個簡單的記憶方法，prior empno 表示向僱員方向探查。

--寫一個相反的例子,從子節點向根節點查詢。prior mgr 表示向領導者方向探查。
SCOTT@test> select * from emp start with empno=7900  connect by prior mgr = empno;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

--還有兩個偽列CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE.

SCOTT@test> select CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,emp.* from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
----------------- ------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                0                  0       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
                0                  0       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
                0                  0       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
                1                  0       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
                0                  0       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
                1                  0       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
                0                  0       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
                1                  0       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
                1                  0       7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
                1                  0       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
                1                  0       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
                1                  0       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
                0                  0       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
                1                  0       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

-- CONNECT_BY_ISLEAF 表示是否是葉子節點(1=葉子 root以及分支=0),CONNECT_BY_ISCYCLE 表示是否存在環(0-不存在 1-存在)。

--如果查詢根節點呢？ 使用函式CONNECT_BY_ROOT

SCOTT@test> select CONNECT_BY_ROOT(empno),CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,emp.* from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
CONNECT_BY_ROOT(EMPNO) CONNECT_BY_ISLEAF CONNECT_BY_ISCYCLE      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------------------- ----------------- ------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                  7839                 0                  0       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
                  7839                 0                  0       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
                  7839                 0                  0       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
                  7839                 1                  0       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
                  7839                 0                  0       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
                  7839                 1                  0       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
                  7839                 0                  0       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
                  7839                 1                  0       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
                  7839                 1                  0       7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
                  7839                 1                  0       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
                  7839                 1                  0       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
                  7839                 1                  0       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
                  7839                 0                  0       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
                  7839                 1                  0       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.

SCOTT@test> select  CONNECT_BY_ROOT(empno),emp.* from emp start with empno=7900  connect by prior mgr = empno;
CONNECT_BY_ROOT(EMPNO)      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                  7900       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
                  7900       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
                  7900       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

--還有一個函式SYS_CONNECT_BY_PATH。

SCOTT@test> select emp.*,SYS_CONNECT_BY_PATH(empno,',') c80 from emp start with mgr is null connect by NOCYCLE prior empno = mgr;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO C80
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------------------------
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10 ,7839
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20 ,7839,7566
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20 ,7839,7566,7788
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20 ,7839,7566,7788,7876
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20 ,7839,7566,7902
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20 ,7839,7566,7902,7369
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30 ,7839,7698
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 ,7839,7698,7499
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 ,7839,7698,7521
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30 ,7839,7698,7654
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30 ,7839,7698,7844
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30 ,7839,7698,7900
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10 ,7839,7782
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10 ,7839,7782,7934
14 rows selected.

--記住這些基本差不多掌握樹形查詢了。

SCOTT@test> select * from (select substr(SYS_CONNECT_BY_PATH(dname,','),2) c50 ,CONNECT_BY_ISLEAF x1  from dept start with deptno=10 connect by prior deptno+10=deptno ) where x1=1;
C50                                                        X1
-------------------------------------------------- ----------
ACCOUNTING,RESEARCH,SALES,OPERATIONS,MARKETING              1