当前位置:Gxlcms > mysql > sys_connect_by_path用法

sys_connect_by_path用法

时间:2021-07-01 10:21:17 帮助过:5人阅读

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

人气教程排行