ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sys_connect_by_path函数

sys_connect_by_path函数

原创 Linux操作系统 作者:sap-barrypan 时间:2009-04-17 20:34:14 0 删除 编辑

转自http://gll--521.blog.163.com/blog/

个人觉这个函数其实很好用,sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下,方法如下 REPLACE(字段名,原字符,',')。

还有,这个函数使用之前必须先建立一个树,否则无用。

举个例子:

目的:将num值相等的项目写成 seq1,seq2,seq3,……的形式

(SELECT num,REPLACE(MAX(sql0), ';', ',')

                          FROM (SELECT num, sys_connect_by_path(sql1, ';') AS sql0

                                  FROM (SELECT num, sql1, rn, lead(rn) over(PARTITION BY num ORDER BY rn) rn1

                                          FROM (SELECT num, sql1, row_number() over(ORDER BY num, sql1 DESC) rn FROM tlsbk))

                                 START WITH num = '1' AND rn1 IS NULL

                                CONNECT BY rn1 = PRIOR rn));

结果:

num REPLACE(MAX(sql0), ';', ',')

--------------------------------------------------------

1 sql0,sql1,sql2

2 sql20,sql21,sql23,sql24,sql25

3 sql30,sql31,sql32,sql33,sql34,sql35,sql36

树结构和它的专用函数SYS_CONNECT_BY_PATH(网摘:http://blog.oracle.com.cn/html/83/t-122083.html)

简单的树型结构

关于树的普通应用

学习了下这个函数, 用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)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16683570/viewspace-590842/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 破解SAP ACCESS KEY
请登录后发表评论 登录
全部评论

注册时间:2008-12-06

  • 博文量
    74
  • 访问量
    182545