Oracle多字段排序详解,ORACLE 多字段排序再合并

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