connect by ...[prior]...start with 学习

学习来源：http://hi.baidu.com/haydo/blog/item/069298438e5c6d1073f05d46.html

SQL> create table test
  2  (id number,p_id number,name varchar2(20));

Table created.

SQL> insert into test values (1,0,'a');

1 row created.

SQL> insert into test values (11,1,'aa');

1 row created.

SQL> insert into test values (12,1,'bb');

1 row created.

SQL> insert into test values (13,1,'cc');

1 row created.

SQL> insert into test values (111,11,'aabb');

1 row created.

SQL> insert into test values (112,11,'aacc');

1 row created.

SQL> insert into test values (131,13,'ccaa');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID       P_ID NAME
---------- ---------- --------------------
       1               0 a
       11            1 aa
       12            1 bb
       13            1 cc
       111          11 aabb
       112          11 aacc
       131          13 ccaa

7 rows selected.

connect by 与 start with 语句摆放的先后顺序不影响查询的结果。

connect by xxx:指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

start with xxx:限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

对prior的说明：
prior可以不要，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式(先找父节点然后找子节点)，后一种写法表示采用自下而上的搜索方式(先找叶子节点然后找父节点)。

自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。
SQL> select id,p_id,name from test connect by prior id=p_id start with p_id=1;

        ID       P_ID NAME
---------- ---------- --------------------
       11           1 aa
       111         11 aabb
       112         11 aacc
       12           1 bb
       13           1 cc
       131         13 ccaa

6 rows selected.

自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
SQL> select id,p_id,name from test connect by id= prior p_id start with p_id=1;

        ID       P_ID NAME
---------- ---------- --------------------
        11          1 aa
        1            0 a
        12          1 bb
        1            0 a
        13          1 cc
        1            0 a

6 rows selected.

SQL> select id,p_id,name from test connect by id= prior p_id start with p_id=11;

        ID       P_ID NAME
---------- ---------- --------------------
       111         11 aabb
       11           1 aa
       1              0 a
       112         11 aacc
       11           1 aa
       1             0 a

6 rows selected.

prior可以不要，不会在寻找这些记录的子节点。
SQL> select id,p_id,name from test connect by id=p_id start with p_id=1;

        ID       P_ID NAME
---------- ---------- --------------------
        11          1 aa
        12          1 bb
        13          1 cc

where条件是在根据"connect by xxx start with xxx"选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构；

SQL> select id,p_id,name from test where p_id <11 connect by prior id=p_id start with p_id=1;

        ID       P_ID NAME
---------- ---------- --------------------
        11          1 aa
        12          1 bb
        13          1 cc

注意：
如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。

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

转载于:http://blog.itpub.net/23135684/viewspace-626499/