sys_connect_by_path 用法-mysql教程-PHP中文网

sys_connect_by_path 用法

php中文网

发布： 2016-06-07 14:56:43

原创

2678人浏览过

sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')。还有，这个函数使用之前必须先建立一个树，否则无用。将num值相等的

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

登录后复制

select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=1
connect by pindid=prior indid

登录后复制

select lpad(' ',6*(level-1))||industry,indlevel,indid,pindid
from ORGINDUSTRIES
start with indid=20
connect by indid=prior pindid;

登录后复制

select areaname,sys_connect_by_path(areaname,',')
from areas bb
start with areaname='中国大陆'
connect by parentareaid=prior areaid

登录后复制

select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid

登录后复制

select areaname,sys_connect_by_path(areaname,',')
from areas bb
where bb.areaid>861000
start with areaname='中国大陆'
connect by parentareaid=prior areaid   and areaname<>'广东'

登录后复制

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

登录后复制

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

登录后复制

select no,q,
   no+row_number() over( order by no) rn,
   row_number() over(partition by no order by no) rn1
from test

登录后复制

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

登录后复制

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

登录后复制