樹結(jié)構(gòu)和它的專用函數(shù)SYS_CONNECT_BY_PATH
簡單的樹型結(jié)構(gòu)
關(guān)于樹的普通應(yīng)用
學(xué)習(xí)了下這個函數(shù), 用ORGINDUSTRIES的表做了個測試:
正常的樹型結(jié)構(gòu)
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=1
connect by pindid=prior indid
結(jié)果顯示如下
Indlevel indid pindid
服裝與服飾 1 1 0
服裝 2 2 1
女裝 3 3 2
倒型樹
下面這個例子是個”倒數(shù)”—倒過來的樹型結(jié)構(gòu)
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=20
connect by indid=prior pindid;
這是標(biāo)準(zhǔn)結(jié)果:
Indlevel indid pindid
二手服裝 3 20 2
服裝 2 2 1
服裝與服飾 1 1 0
結(jié)論
無論正樹還是倒樹, 關(guān)鍵就在于connect by的條件.
正樹: 必須是 ‘父’= prior ‘子’
倒樹: 必須是 ‘子’= prior ‘父’
樹型結(jié)構(gòu)的條件過濾
采用樹型結(jié)構(gòu)的話, 如果我們想將樹上的一個分支砍掉. 將分支后面的結(jié)構(gòu)都拋棄掉, 這個可以實現(xiàn)麼？當(dāng)然可以。 但是不是用where， where條件只能去除單一的條件。
所以， 這種樹型的過濾條件就需要加在connect by上面。
測試如下：由于用真實環(huán)境比較貼近實際，所以提前用下SYS_CONNECT_BY_PATH函數(shù)來顯示下環(huán)境
不加任何條件的環(huán)境：
select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中國大陸'
connect by parentareaid=prior areaid
結(jié)果：
1 中國大陸,中國大陸
2 北京 ,中國大陸,北京
3 北京 ,中國大陸,北京,北京
4 東城區(qū) ,中國大陸,北京,東城區(qū)
5 西城區(qū) ,中國大陸,北京,西城區(qū)
22 廣東 ,中國大陸,廣東
23 廣州 ,中國大陸,廣東,廣州
24 汕尾 ,中國大陸,廣東,汕尾
25 潮陽 ,中國大陸,廣東,潮陽
46 上海 ,中國大陸,上海
47 上海 ,中國大陸,上海,上海
48 黃浦區(qū) ,中國大陸,上海,黃浦區(qū)
49 閘北區(qū) ,中國大陸,上海,閘北區(qū)
加了where過濾條件的SQL:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中國大陸'
connect by parentareaid=prior areaid
結(jié)果為：
2 北京 ,中國大陸,北京
3 北京 ,中國大陸,北京,北京
4 東城區(qū) ,中國大陸,北京,東城區(qū)
5 西城區(qū) ,中國大陸,北京,西城區(qū)
22 廣東 ,中國大陸,廣東
23 廣州 ,中國大陸,廣東,廣州
24 汕尾 ,中國大陸,廣東,汕尾
25 潮陽 ,中國大陸,廣東,潮陽
46 上海 ,中國大陸,上海
47 上海 ,中國大陸,上海,上海
48 黃浦區(qū) ,中國大陸,上海,黃浦區(qū)
49 閘北區(qū) ,中國大陸,上海,閘北區(qū)
結(jié)論：去掉了“1 中國大陸,中國大陸”數(shù)據(jù)
加了connect by的過濾條件：
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中國大陸'
connect by parentareaid=prior areaid and areaname<>'廣東'
結(jié)果為：
2 北京 ,中國大陸,北京
3 北京 ,中國大陸,北京,北京
4 東城區(qū) ,中國大陸,北京,東城區(qū)
5 西城區(qū) ,中國大陸,北京,西城區(qū)
46 上海 ,中國大陸,上海
47 上海 ,中國大陸,上海,上海
48 黃浦區(qū) ,中國大陸,上海,黃浦區(qū)
49 閘北區(qū) ,中國大陸,上海,閘北區(qū)
結(jié)論：去掉了整個廣東的分支， 在結(jié)果集中只有北京和上海
SYS_CONNECT_BY_PATH函數(shù)
采用SYS_CONNECT_BY_PATH函數(shù)為:
select industry,sys_connect_by_path(industry,'/')
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;
結(jié)果為:
女裝 /女裝
服裝 /女裝/服裝
服裝與服飾 /女裝/服裝/服裝與服飾
這樣的話, 就可以實現(xiàn), 樹結(jié)構(gòu)的結(jié)果集的單行拼接:
我們只需要取最大的字段就OK了
測試如下：
select max(sys_connect_by_path(industry,'/'))
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;
結(jié)果為：
/女裝/服裝/服裝與服飾
復(fù)雜的樹型結(jié)構(gòu)――多列變單列
樹型結(jié)構(gòu)也分單樹和多樹(我的稱呼,實際上就是指單支和多支)
對于下面的這種情況， 我們必須要構(gòu)造的樹就屬于單支樹。
原始環(huán)境
環(huán)境如下：
select * from test；
結(jié)果為：
1 n1
1 n2
1 n3
1 n4
1 n5
3 t1
3 t2
3 t3
3 t4
3 t5
3 t6
2 m1
造樹
腳本如下：
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
結(jié)果如下：
No Q RN RN1
1 n1 2 1
1 n2 3 2
1 n3 4 3
1 n4 5 4
1 n5 6 5
2 m1 8 1
3 t1 10 1
3 t2 11 2
3 t3 12 3
3 t4 13 4
3 t5 14 5
3 t6 15 6
每列的目的是：
RN1列主要的目的是分組， 按照value值‘1’，我們可以start with使用它。
RN列主要用來做connect by使用。 實際上它就是我們要的樹。
第一個支： 2，3，4，5，6
第二個支： 8
第三個支： 10，11，12，13，14，15
中間為什么要斷掉：7,9 目的就是為了區(qū)別每個分支。 到后面看具體的SQL，就明白這里的說法了。
殺手锏
既然我們有了樹， 就可以使用樹型函數(shù)SYS_CONNECT_BY_PATH和connect by啦，來拼接我們所需要的多列值。
腳本如下：
select no,sys_connect_by_path(q,',')
from (
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
結(jié)果為：
1 ,n1
1 ,n1,n2
1 ,n1,n2,n3
1 ,n1,n2,n3,n4
1 ,n1,n2,n3,n4,n5
2 ,m1
3 ,t1
3 ,t1,t2
3 ,t1,t2,t3
3 ,t1,t2,t3,t4
3 ,t1,t2,t3,t4,t5
3 ,t1,t2,t3,t4,t5,t6
終極武器
最終我們要的值，是單列值， 其實想想， 也就是最長的一行咯。 那么就好辦了。 我們直接GROUP BY ，然后取MAX值。
腳本如下：
select no,max(sys_connect_by_path(q,','))
from (
select no,q,
no+row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from test
)
start with rn1=1
connect by rn-1=prior rn
group by no
結(jié)果為：
1 ,n1,n2,n3,n4,n5
2 ,m1
3 ,t1,t2,t3,t4,t5,t6
如果覺得前面的‘，’不好看，可以使用ltrim去掉。 或者用substr也可以。
如下：
ltrim(max(sys_connect_by_path(q,',')),',')
或者
substr(max(sys_connect_by_path(q,',')),2)