oracle 的 CONNECT BY 和START WITH

htudfjsqhk

在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伪列，这些伪列都是从可用性角度提出来的。稍作组合便可以得到意想不到的效果。更多有趣的细节请大家慢慢体会。