有关层次查询之前的文章参考如下。
【层次查询】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伪列
分享好友
分享这个小栈给你的朋友们,一起进步吧。
订阅须知
• 所有用户可根据关注领域订阅专区或所有专区
• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询
• 专区发布评论属默认订阅所评论专区(除付费小栈外)