oracle 的 CONNECT BY 和START WITH

在SELECT命令中使用CONNECT BY 和START WITH 子句可以查詢表中的樹形結構關係。其命令格式如下：
SELECT ….. CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH]；
其中：CONNECT BY子句說明每行資料將是按層次順序檢索，並規定將表中的資料連入樹形結構的關係中。
0PRIORY運算子必須放置在串連關係的兩列中的某一個的前面。對於節點間的父子關係，PRIOR去處符在一側表示父節點，
在別一側表示子節點，從而確定尋找權結構的順序是自頂向下還是自底向上。在串連關係中，除了可以使用列名外，
還允許使用列運算式。START WITH 子句為可選項，用來標識哪個節點作為尋找樹型結構的根節點。

例子：

create table test2(empno number,ename varchar2(30),managerid number);

insert into test2 values(10000,'martin',0);
insert into test2 values(10001,'binn',10000);
insert into test2 values(10002,'davit',10001);
insert into test2 values(10003,'kyte',10002);
insert into test2 values(10004,'shre',10003);
insert into test2 values(10006,'ivan',10002);
insert into test2 values(10007,'richard',10003);

select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'<-') router
from test2
start with ename='martin'
--connect by empno=prior managerid
connect by prior empno=managerid
order by level desc;

select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
from test2
start with ename='richard'
connect by empno=prior managerid
order by level desc;

總結：

所以，上述規則可以描述為：
01) 當操作符prior在parent前面時,得到child =>parent關係樹

當prior在child前面時，得到parent =>child關係樹

02) 當指定了start with時，過濾掉沒有包含指定欄位的所有關係;否則，不作任何過濾,顯示所有關係

最簡單實際使用小例子：

在有些項目中，下拉中用資料來源取年份時，可用如下方式寫：

select * from
(select rownum,to_char(add_months(sysdate, 4), 'yyyy') - rownum
from dual connect by rownum<5);

注意：用此函數多表關聯出現問題與ORACLE版本有關，例子如下：

create or replace view VIEW_T1 as
/*
* 816不支援這種寫法,9201下這種寫法，在以Fld3為條件做查詢時會導致記錄數不正確
SELECT *
FROM T1 A, TCODE B
WHERE A.Fld1 = B.Fld1(+)
AND A.Fld3 = B.C_DM(+)
START WITH Fld2 IS NULL
CONNECT BY PRIOR Fld2_Farther= Fld2
*/
SELECT A.*, nvl(B.C_MC, A.Fld3) Fld3_MC from
(
SELECT *
FROM T1 A
START WITH Fld2 IS NULL
CONNECT BY PRIOR Fld2_Farther= Fld2
) A, TCODE B
WHERE A.Fld1 = B.Fld1(+)
AND A.Fld3 = B.C_DM(+)

=================================================================================

有關 層次查詢之前的文章參考如下。
【層次查詢】Hierarchical Queries之LEVEL應用
http://space.itpub.net/519536/viewspace-623916
【層次查詢】Hierarchical Queries之“樹的遍曆”
http://space.itpub.net/519536/viewspace-623809
【層次查詢】Hierarchical Queries之CONNECT_BY_ISCYCLE偽列
http://space.itpub.net/519536/viewspace-624032


繼LEVEL和CONNECT_BY_ISCYCLE偽列之後，最後一個可用的 偽列名字叫做CONNECT_BY_ISLEAF，正如這個偽列名字中描述的那樣，他可以指示出哪些是“葉子節點”，就是這麼簡單。
如果發現是葉子節點，該偽列會返回“1”，反之返回記錄“0”。

1.回望那棵關係“樹”，很直觀，F、G和E節點是葉子節點。
      A
     / \
    B   C
   /   /
  D   E
 / \
F   G

2.重溫一下闡述的T表資料
sec@ora10g> select * from t;

X                   Y          Z
---------- ---------- ----------
A                   1
B                   2          1
C                   3          1
D                   4          2
E                   5          3
F                   6          4
G                   7          4

7 rows selected.

3.結合LEVEL偽列看一下CONNECT_BY_ISLEAF的直觀效果
sec@ora10g> col tree for a16
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF from t start with x = 'A' connect by NOCYCLE prior y=z;

TREE             CONNECT_BY_ISLEAF
---------------- -----------------
A                                0
  B                              0
    D                            0
      F                          1
      G                          1
  C                              0
    E                            1

7 rows selected.

是不是有一種豁然開朗的感覺，對頭，CONNECT_BY_ISLEAF功能就是這麼簡單。

4.如何僅篩選出上面結果中所有葉子節點？
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z;

TREE             CONNECT_BY_ISLEAF
---------------- -----------------
      F                          1
      G                          1
    E                            1

5.得到每一層級葉子節點
可以理解下面的 SQL是對“樹”的逐層消除的過程。
1）不做消除，因為我們的樹只有4層。
（1）SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=4;

TREE             CONNECT_BY_ISLEAF      LEVEL
---------------- ----------------- ----------
      F                          1          4
      G                          1          4
    E                            1          3

（2）對應的“樹”
      A
     / \
    B   C
   /   /
  D   E
 / \
F   G

2）消除第四層
（1）SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=3;

TREE             CONNECT_BY_ISLEAF      LEVEL
---------------- ----------------- ----------
    D                            1          3
    E                            1          3

（2）對應的“樹”
      A
     / \
    B   C
   /   /
  D   E

3）消除第三層
（1）SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=2;

TREE             CONNECT_BY_ISLEAF      LEVEL
---------------- ----------------- ----------
  B                              1          2
  C                              1          2

（2）對應的“樹”
      A
     / \
    B   C

4）消除第二層
（1）SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=1;

TREE             CONNECT_BY_ISLEAF      LEVEL
---------------- ----------------- ----------
A                                1          1

（2）對應的“樹”，此時只剩根節點了。
      A

6.小結
有關層次查詢中可用的偽列有LEVEL、CONNECT_BY_ISCYCLE還是本文所述的CONNECT_BY_ISLEAF偽列，這些偽列都是從可用性角度提出來的。稍作組合便可以得到意想不到的效果。更多有趣的細節請大家慢慢體會。