ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【层次查询】Hierarchical Queries之CONNECT_BY_ISLEAF伪列

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

Linux操作系统 作者:abraham_dba_2013 时间: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


继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 --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29439655/viewspace-1091590/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-13

  • 博文量
    90
  • 访问量
    395932