【层次查询】Hierarchical Queries之CONNECT_BY_ISLEAF伪列

Linux操作系统 时间：2014-02-26 14:05:21 0 删除 编辑

【层次查询】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

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.

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.得到每一层级叶子节点

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.小结

Good luck.

secooler
09.12.31

-- The End --

• 博文量
90
• 访问量
395932