1、创建表并插入数据 SQL>createtable test(a number,b varchar2(20)); SQL>select*from test; A B ---------- -------------------- 1 a 1 b 1 c 1 d 1 e 2 a 2 b 3 a 3 v 3 d 10 rows selected
查看合并列结果 select a,max(sys_connect_by_path(b,';')) result from (select a,b, (row_number() over(orderby a,b desc) --row_number()-1 over(order by a,b desc) rn1 +dense_rank() over(orderby a)) rn, max(b) over(partition by a) bs from test) start with b=bs connect by rn-1= prior rn groupby a; 结果如下: A RESULT ---------- -------------------------------------------------------------------------------- 1 ;e;d;c;b;a 2 ;b;a 3 ;v;d;a
或者 select a,max(sys_connect_by_path(b,';')) result from (select a,b, row_number() over(orderby a,b desc) rn, row_number() over(orderby a,b desc)-1 rn1, --+dense_rank() over(order by a)) rn, max(b) over(partition by a) bs from test) start with b=bs connect by rn1 = prior rn and a = prior a groupby a;
注释: select a,b,rn,lead(rn) over(partition by a orderby rn) rn1 from (select a,b,row_number() over(orderby a,b desc) rn from test) 结果如下: A B RN RN1 ---------- -------------------- ---------- ---------- 1 e 12 1 d 23 1 c 34 1 b 45 1 a 5 2 b 67 2 a 7 3 v 89 3 d 910 3 a 10 10 rows selected select a ,sys_connect_by_path(b,';') result from (select a,b,rn,lead(rn) over(partition by a orderby rn) rn1 from (select a,b,row_number() over(orderby a,b desc) rn from test)) start with a=1and rn1 isnull connect by rn1=prior rn; 结果如下: A RESULT ---------- -------------------------------------------------------------------------------- 1 ;a 1 ;a;b 1 ;a;b;c 1 ;a;b;c;d 1 ;a;b;c;d;e