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

注意：
如果表中的記錄不是嚴格遵照每條記錄都只能有一個父記錄的原則，那麼就可能有部分記錄會存在於多棵樹中，那麼在查詢記錄的時候就可能會出現找到多條重複記錄的異常情況。