有關層次查詢之前的文章參考如下。
【層次查詢】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偽列，這些偽列都是從可用性角度提出來的。稍作組合便可以得到意想不到的效果。更多有趣的細節請大家慢慢體會。

今天是2009年的最後一天，祝願每一位好朋友新年快樂，吉祥如意，身體健康。

Good luck.

secooler
09.12.31

-- The End --

【層次查詢】Hierarchical Queries之CONNECT_BY_ISLEAF偽列

相關文章