Oracle start with connect by prior... 遞迴查詢

標籤：分享   span   code   desc   技術分享   creat   with   varchar2   into   

start with connect by prior 主要是用於B樹結構類型的資料遞迴查詢，給出B樹結構類型中的任意一個節點，遍曆其最終父節點或者子節點。

-- create tablecreate table prior_test( parentid number(10),  subid    number(10));-- 欄位類型最好用 number，而不是 varchar2，因為測試 SQL 需要比較 id-- insertinsert into prior_test values ( 1, 2 );insert into prior_test values ( 1, 3 );insert into prior_test values ( 2, 4 );insert into prior_test values ( 2, 5 );insert into prior_test values ( 3, 6 );insert into prior_test values ( 3, 7 );insert into prior_test values ( 5, 8 );insert into prior_test values ( 5, 9 );insert into prior_test values ( 7, 10 );insert into prior_test values ( 7, 11 );insert into prior_test values ( 10, 12 );insert into prior_test values ( 10, 13 );

 

-- selectselect * from prior_test

 

-- SQL-1select t.parentid, t.subid, level from prior_test tstart with t.subid = 7connect by subid = prior parentidorder by level desc

 

-- SQL-2select t.parentid, t.subid, level from prior_test tstart with t.subid = 7connect by prior subid = parentidorder by level desc

SQL 解析：

start with 子句：遍曆起始條件

connect by 子句：串連條件

　　關鍵詞 prior，prior 跟父節點列 parentid 放在一起，prior parentid 就是往父節點方向遍曆；prior跟子節點列 subid 放在一起，prior subid 則往子節點方向遍曆。

　　parentid、subid 兩列誰放在 ‘=‘ 前都無所謂，關鍵是 prior 後面的欄位。（比較上面查詢語句 SQL-1 與 SQL-2）

order by 子句：排序

 

觀察下面 SQL-3 與 SQL-4 分別執行的結果集：

-- SQL-3select t.parentid, t.subid, level from prior_test tstart with t.subid = 7connect by prior subid = parentidorder by level desc

 

-- SQL-4select t.parentid, t.subid, level from prior_test tstart with t.parentid = 7connect by prior subid = parentidorder by level desc

結論：start with subid 與 start with parentid 結果集是不同的。

 

加入 where 子句

-- SQL-5select t.parentid, t.subid, level from bb_test twhere t.parentid > 3start with t.subid = 12connect by subid = prior parentidorder by level desc

SQL 執行順序是：先 start with 子句, 在是 connect by 子句, 最後是 where 子句！

where 只是樹結構的修剪，不改變樹的階層。

Oracle start with connect by prior... 遞迴查詢