树结构和它的专用函数SYS_CONNECT_BY_PATH

Posted on 2009-09-20 21:32 林光炎 阅读(1206) 评论(0)  编辑  收藏 所属分类: ORACLE
简单的树型结构
关于树的普通应用
学习了下这个函数, 用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

倒型树
下面这个例子是个”倒数”—倒过来的树型结构
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 ‘父’

树型结构的条件过滤
采用树型结构的话, 如果我们想将树上的一个分支砍掉.  将分支后面的结构都抛弃掉, 这个可以实现麽?当然可以。 但是不是用where, where条件只能去除单一的条件。
所以, 这种树型的过滤条件就需要加在connect by上面。

测试如下:由于用真实环境比较贴近实际,所以提前用下SYS_CONNECT_BY_PATH函数来显示下环境

不加任何条件的环境:
select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中国大陆'
connect by parentareaid=prior areaid  

结果:
1        中国大陆,中国大陆
2        北京        ,中国大陆,北京
3        北京        ,中国大陆,北京,北京
4        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
22        广东        ,中国大陆,广东
23        广州        ,中国大陆,广东,广州
24        汕尾        ,中国大陆,广东,汕尾
25        潮阳        ,中国大陆,广东,潮阳
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区


加了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        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
22        广东        ,中国大陆,广东
23        广州        ,中国大陆,广东,广州
24        汕尾        ,中国大陆,广东,汕尾
25        潮阳        ,中国大陆,广东,潮阳
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区

结论:去掉了“1        中国大陆,中国大陆”数据

加了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        东城区        ,中国大陆,北京,东城区
5        西城区        ,中国大陆,北京,西城区
46        上海        ,中国大陆,上海
47        上海        ,中国大陆,上海,上海
48        黄浦区        ,中国大陆,上海,黄浦区
49        闸北区        ,中国大陆,上海,闸北区

结论:去掉了整个广东的分支,  在结果集中只有北京和上海


SYS_CONNECT_BY_PATH函数
采用SYS_CONNECT_BY_PATH函数为:

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

结果为:
女装               /女装
服装               /女装/服装
服装与服饰            /女装/服装/服装与服饰

这样的话, 就可以实现, 树结构的结果集的单行拼接:

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

测试如下:

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

结果为:
/女装/服装/服装与服饰


复杂的树型结构――多列变单列
树型结构也分单树和多树(我的称呼,实际上就是指单支和多支)
对于下面的这种情况, 我们必须要构造的树就属于单支树。
原始环境
环境如下:
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  目的就是为了区别每个分支。 到后面看具体的SQL,就明白这里的说法了。

杀手锏
既然我们有了树, 就可以使用树型函数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)

posts - 104, comments - 33, trackbacks - 0, articles - 0

Copyright © 林光炎