mysql sys_connect_by_path_sys_connect_by_path 用法

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！