sys_connect_by_path用法

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

(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