簡單的樹型結構
關于樹的普通應用
學習了下這個函數(shù), 用ORGINDUSTRIES的表做了個測試:
正常的樹型結構
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=1
connect by pindid=prior indid
結果顯示如下
                 Indlevel  indid    pindid
        服裝與服飾               1             1             0
              服裝               2             2               1
                    女裝        3             3               2

倒型樹
下面這個例子是個”倒數(shù)”—倒過來的樹型結構
select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=20
connect by indid=prior pindid;
這是標準結果:
                             Indlevel indid    pindid
二手服裝                      3        20       2
      服裝                    2        2        1
            服裝與服飾        1        1        0
結論
無論正樹還是倒樹, 關鍵就在于connect by的條件.
正樹:  必須是  ‘父’= prior ‘子’
倒樹:  必須是  ‘子’= prior ‘父’

樹型結構的條件過濾
采用樹型結構的話, 如果我們想將樹上的一個分支砍掉.  將分支后面的結構都拋棄掉, 這個可以實現(xiàn)麼？當然可以。 但是不是用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  

結果：
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

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

結論：去掉了“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<>'廣東'

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

結論：去掉了整個廣東的分支，  在結果集中只有北京和上海


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;

結果為:
女裝               /女裝
服裝               /女裝/服裝
服裝與服飾            /女裝/服裝/服裝與服飾

這樣的話, 就可以實現(xiàn), 樹結構的結果集的單行拼接:

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

測試如下：

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

結果為：
/女裝/服裝/服裝與服飾


復雜的樹型結構――多列變單列
樹型結構也分單樹和多樹(我的稱呼,實際上就是指單支和多支)
對于下面的這種情況， 我們必須要構造的樹就屬于單支樹。
原始環(huán)境
環(huán)境如下：
select * from test；

結果為：
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

結果如下：
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

結果為：
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

結果為：
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)