Hierarchical Queries 級聯查詢(樹狀結構查詢)

tthero00boo發表於2013-09-26
 
語法:
select * from tab [where 條件1] start with [條件2] connect by [條件3] ;

其中 connect by 與 start with 語句擺放的先後順序不影響查詢的結果，[where 條件1]可以不需要。
[where 條件1]、[條件2]、[條件3]各自作用的範圍都不相同：

[where 條件1]是在根據“start with [條件2] connect by [條件3]”選擇出來的記錄中進行過濾，是針對單條記錄的過濾， 不會影響構造樹；

[條件2]限定作為搜尋起始點的條件，如果是自上而下的搜尋則是限定作為根節點的條件，如果是自下而上的搜尋則是限定作為葉子節點的條件；

[條件3]指定構造樹的條件，以及對樹分支的過濾條件，在這裡執行的過濾會把符合條件的記錄及其下的所有子節點都過濾掉；

select * from t
where t.id!=123
start with p_id = 0
connect by prior id = p_id;

prior有兩種寫法,connect by prior id = p_id 或 connect by p_id = prior id     
前一種寫法表示採用自上而下的搜尋方式（先找父節點然後找子節點）
後一種寫法表示採用自下而上的搜尋方式（先找葉子節點然後找父節點）

CONNECT_BY_ROOT 用在列名之前用於返回當前層的根節點 ,CONNECT_BY_ROOT name p_name /*返回該行的根節點的列的值*/
SYS_CONNECT_BY_PATH(value,'>')函式，我們可以得到 層次結構或者說樹狀結構 的路徑
value是要連線的值(列,表示式),'>'是連線符
CONNECT_BY_ISLEAF 偽列 (0-存在孩子,1-leaf),LEAF（葉子），即沒有孩子節點
LEVEL 偽列

引入結構化查詢後，SQL語句的執行順序問題，根據Oracle文件，先後是：

1）JOIN，無論用的是JOIN ON的寫法，還是在WHERE中做的關聯

2）CONNECT BY

3）其它的WHERE條件

所以懶得記...還是套一張表好了,先where查出結果集,作為子表再構造樹

select b.parentid p_id,
       CONNECT_BY_ROOT name p_name,
       b.numid cnt_child,
       level, /*lpad('└ '||dataid,(4*level),' '),*/
       connect_by_isleaf isleaf,
       sys_connect_by_path(dataid, '>') all_path
  from ( select a.parentid,
               a.name,
               a.dataid,
               row_number() over(partition by a.parentid order by a.sequence) numid
          from datadic a) b
 where connect_by_isleaf = 1
 start with b.numid = 1
connect by prior numid = b.numid - 1
       and prior parentid = parentid;
 P_ID P_NAME         CNT_CHILD LEVEL ISLEAF ALL_PATH
----- ------------- ---------- ----- ------ ------------------------------------
    0 A                      2     2      1 >1>2
    1 A--1                   2     2      1 >3>4
    3 A--1--1                1     1      1 >5
    5 A--1--1--1             2     2      1 >140>150
  140 A--1--1--1--1          9     9      1 >141>142>143>144>145>146>147>148>149
  150 A--1--1--2--1          9     9      1 >151>152>153>154>155>156>157>158>159

其他:
select rownum from dual connect by level < 10000; /* 插表時很好用 */

可以實現行轉列

select ww.*,sys_connect_by_path(name ,',') from
(
with
w1 as ( select 'a1' as name from dual
union select 'b2' from dual
union select 'c3' from dual
)
select w1.* ,rownum id from w1 
) ww
start with id = 1
connect by prior id = id - 1;

NAME         ID SYS_CONNECT_BY_PATH(NAME,',')
---- ---------- --------------------------------
a1            1 ,a1
b2            2 ,a1,b2
c3            3 ,a1,b2,c3


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28859270/viewspace-773430/，如需轉載，請註明出處，否則將追究法律責任。

相關文章