PHP code:
SQL> DESC TEST
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A NUMBER Y
B VARCHAR2(20) Y
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
想要的结果:
A RESULT
---------- --------------------------------------------------------------------------------
1 e;d;c;b;a
2 b;a
3 v;d;a
有以下两种方法：
方法1：
select a,substr(max(sys_connect_by_path(b,';')),2) result
from
(select a,b,
row_number() over(order by a,b desc) rn,
row_number() over(order by 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
group by a;
方法2：
select a,max(sys_connect_by_path(b,';')) result
from
(select a,b,
(row_number() over(order by a,b desc)
--row_number()-1 over(order by a,b desc) rn1
+dense_rank() over(order by a)) rn,
max(b) over(partition by a) bs
from test)
start with b=bs
connect by rn-1 = prior rn
group by a;
