drop table temp ;
create table temp ( id number , st varchar2(100), end varchar2(100)) ;
insert into temp values( 1 , 11,null) ;
insert into temp values( 1 , 66,null) ;
insert into temp values( 1 , 33,null) ;
insert into temp values( 1 , null,22) ;
insert into temp values( 1 , null,55) ;
insert into temp values( 2 , 33,null) ;
insert into temp values( 2 , 77,null) ;
insert into temp values( 2 , 11,null) ;
insert into temp values( 2 , null,22) ;
insert into temp values( 2 , null,55) ;
insert into temp values( 2 , null,99) ;
commit;
select * from temp ;
select t2.id
,regexp_replace(regexp_replace(ltrim(max(sys_connect_by_path(st,',')),','),'[,]{2,}',','),',$','')
,regexp_replace(regexp_replace(ltrim(max(sys_connect_by_path(end,',')),','),'[,]{2,}',','),',$','')
from (
select t1.*, lead(le1) over(partition by id order by le1 desc) le2 from
( select T.*, ROW_NUMBER() OVER( order by st||end) le1 from TEMP T ) t1 ) t2
start with le2 is null
connect by le2 = prior le1
group by id
源表数据:
select * from temp ;
ID STEND
1111
2166
3133
4122
5155
6233
7277
8211
9222
10255
11299
执行结果:
11 11,33,6622,55
2211,33,7722,55,99
上述SQL 在 11G中运行没问题, 在10G环境需改为:
with temp_data as (
select t1.*, lead(le1) over(partition by id order by le1 desc) le2 from
( select T.*, ROW_NUMBER() OVER( order by st||end) le1 from TEMP T ) t1
)
select t2.id
,regexp_replace(regexp_replace(ltrim(max(sys_connect_by_path(st,',')),','),'[,]{2,}',','),',$','')
,regexp_replace(regexp_replace(ltrim(max(sys_connect_by_path(end,',')),','),'[,]{2,}',','),',$','')
from temp_data t2
start with le2 is null
connect by le2 = prior le1
group by id