The blog of Astro Qi

簡(jiǎn)單的樹(shù)型結(jié)構(gòu)
關(guān)于樹(shù)的普通應(yīng)用
學(xué)習(xí)了下這個(gè)函數(shù), 用ORGINDUSTRIES的表做了個(gè)測(cè)試:
正常的樹(shù)型結(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ù)
下面這個(gè)例子是個(gè)”倒數(shù)”—倒過(guò)來(lái)的樹(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é)論
無(wú)論正樹(shù)還是倒樹(shù), 關(guān)鍵就在于connect by的條件.
正樹(shù):  必須是  ‘父’= prior ‘子’
倒樹(shù):  必須是  ‘子’= prior ‘父’

樹(shù)型結(jié)構(gòu)的條件過(guò)濾
采用樹(shù)型結(jié)構(gòu)的話, 如果我們想將樹(shù)上的一個(gè)分支砍掉.  將分支后面的結(jié)構(gòu)都拋棄掉, 這個(gè)可以實(shí)現(xiàn)麼？當(dāng)然可以。 但是不是用where， where條件只能去除單一的條件。
所以， 這種樹(shù)型的過(guò)濾條件就需要加在connect by上面。

測(cè)試如下：由于用真實(shí)環(huán)境比較貼近實(shí)際，所以提前用下SYS_CONNECT_BY_PATH函數(shù)來(lái)顯示下環(huán)境

不加任何條件的環(huán)境：
select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中國(guó)大陸'
connect by parentareaid=prior areaid  

結(jié)果：
1        中國(guó)大陸,中國(guó)大陸
2        北京        ,中國(guó)大陸,北京
3        北京        ,中國(guó)大陸,北京,北京
4        東城區(qū)        ,中國(guó)大陸,北京,東城區(qū)
5        西城區(qū)        ,中國(guó)大陸,北京,西城區(qū)
22        廣東        ,中國(guó)大陸,廣東
23        廣州        ,中國(guó)大陸,廣東,廣州
24        汕尾        ,中國(guó)大陸,廣東,汕尾
25        潮陽(yáng)        ,中國(guó)大陸,廣東,潮陽(yáng)
46        上海        ,中國(guó)大陸,上海
47        上海        ,中國(guó)大陸,上海,上海
48        黃浦區(qū)        ,中國(guó)大陸,上海,黃浦區(qū)
49        閘北區(qū)        ,中國(guó)大陸,上海,閘北區(qū)

加了where過(guò)濾條件的SQL:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中國(guó)大陸'
connect by parentareaid=prior areaid

結(jié)果為：
2        北京        ,中國(guó)大陸,北京
3        北京        ,中國(guó)大陸,北京,北京
4        東城區(qū)        ,中國(guó)大陸,北京,東城區(qū)
5        西城區(qū)        ,中國(guó)大陸,北京,西城區(qū)
22        廣東        ,中國(guó)大陸,廣東
23        廣州        ,中國(guó)大陸,廣東,廣州
24        汕尾        ,中國(guó)大陸,廣東,汕尾
25        潮陽(yáng)        ,中國(guó)大陸,廣東,潮陽(yáng)
46        上海        ,中國(guó)大陸,上海
47        上海        ,中國(guó)大陸,上海,上海
48        黃浦區(qū)        ,中國(guó)大陸,上海,黃浦區(qū)
49        閘北區(qū)        ,中國(guó)大陸,上海,閘北區(qū)

結(jié)論：去掉了“1        中國(guó)大陸,中國(guó)大陸”數(shù)據(jù)

加了connect by的過(guò)濾條件：
select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中國(guó)大陸'
connect by parentareaid=prior areaid  and areaname<>'廣東'

結(jié)果為：
2        北京        ,中國(guó)大陸,北京
3        北京        ,中國(guó)大陸,北京,北京
4        東城區(qū)        ,中國(guó)大陸,北京,東城區(qū)
5        西城區(qū)        ,中國(guó)大陸,北京,西城區(qū)
46        上海        ,中國(guó)大陸,上海
47        上海        ,中國(guó)大陸,上海,上海
48        黃浦區(qū)        ,中國(guó)大陸,上海,黃浦區(qū)
49        閘北區(qū)        ,中國(guó)大陸,上海,閘北區(qū)

結(jié)論：去掉了整個(gè)廣東的分支，  在結(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é)果為:
女裝               /女裝
服裝                    /女裝/服裝
服裝與服飾            /女裝/服裝/服裝與服飾

這樣的話, 就可以實(shí)現(xiàn), 樹(shù)結(jié)構(gòu)的結(jié)果集的單行拼接:

我們只需要取最大的字段就OK了

測(cè)試如下：

select max(sys_connect_by_path(industry,'/'))
from ORGINDUSTRIES
start with indid=3
connect by indid=prior pindid;

結(jié)果為：
/女裝/服裝/服裝與服飾

復(fù)雜的樹(shù)型結(jié)構(gòu)――多列變單列
樹(shù)型結(jié)構(gòu)也分單樹(shù)和多樹(shù)(我的稱呼,實(shí)際上就是指單支和多支)
對(duì)于下面的這種情況， 我們必須要構(gòu)造的樹(shù)就屬于單支樹(shù)。
原始環(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

造樹(shù)
腳本如下：
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列主要用來(lái)做connect by使用。 實(shí)際上它就是我們要的樹(shù)。
第一個(gè)支： 2，3，4，5，6
第二個(gè)支： 8
第三個(gè)支： 10，11，12，13，14，15

中間為什么要斷掉：7,9  目的就是為了區(qū)別每個(gè)分支。 到后面看具體的SQL，就明白這里的說(shuō)法了。

殺手锏
既然我們有了樹(shù)， 就可以使用樹(shù)型函數(shù)SYS_CONNECT_BY_PATH和connect by啦，來(lái)拼接我們所需要的多列值。

腳本如下：
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

終極武器
最終我們要的值，是單列值， 其實(shí)想想， 也就是最長(zhǎng)的一行咯。 那么就好辦了。 我們直接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

如果覺(jué)得前面的‘，’不好看，可以使用ltrim去掉。 或者用substr也可以。
如下：
ltrim(max(sys_connect_by_path(q,',')),',')
或者
substr(max(sys_connect_by_path(q,',')),2)