select max(substr((sys_connect_by_path(a2,'')),2)) cola from ( select a1,a2, rownum rnum, row_number() over(partition by a1 order by a1) rn1 from a ) start with rn1=1 connect by rnum-1=prior rnum group by a1
[code=SQL]SQL> with b as (select 1 a1,'a' a2 from dual 2 union 3 select 1 a1,'b' a2 from dual 4 union 5 select 2 a1,'x' a2 from dual 6 union 7 select 2 a1,'y' a2 from dual 8 union 9 select 2 a1,'z' a2 from dual 10 ) 11 select a1,max(sys_connect_by_path(a2,' ')) aa from 12 (select a1,a2,rownum rnum,row_number()over(partition by a1 order by a1,a2) rn from b) 13 start with rn=1 14 connect by rnum-1=prior rnum 15 group by a1 16 ;
A1 AA ---------- -------------------------------------------------------------------------------- 1 a b 2 x y z[/code]
想使用函数把一组的数据相加,
之后使用select 的到你要的结果
pl-sql不熟
select max(substr((sys_connect_by_path(a2,'')),2)) cola
from (
select a1,a2,
rownum rnum,
row_number() over(partition by a1 order by a1) rn1
from a
)
start with rn1=1
connect by rnum-1=prior rnum
group by a1
2 union
3 select 1 a1,'b' a2 from dual
4 union
5 select 2 a1,'x' a2 from dual
6 union
7 select 2 a1,'y' a2 from dual
8 union
9 select 2 a1,'z' a2 from dual
10 )
11 select a1,max(sys_connect_by_path(a2,' ')) aa from
12 (select a1,a2,rownum rnum,row_number()over(partition by a1 order by a1,a2) rn from b)
13 start with rn=1
14 connect by rnum-1=prior rnum
15 group by a1
16 ;
A1 AA
---------- --------------------------------------------------------------------------------
1 a b
2 x y z[/code]