select name,ltrim(max(sys_connect_by_path(id,'、')) keep (dense_rank last order by curr),'、' )as id
from
(select name,id,row_number() over (partition by name order by name) as curr,
row_number() over (partition by name order by name)-1 as prev from test)
group by name
connect by prev=prior curr and name=prior name start with curr=1
from
(select name,id,row_number() over (partition by name order by name) as curr,
row_number() over (partition by name order by name)-1 as prev from test)
group by name
connect by prev=prior curr and name=prior name start with curr=1
FROM (
SELECT name,id, ROW_NUMBER()OVER (PARTITION BY name ORDER BY id)rn
FROM B)
GROUP BY name
START WITH rn=1
CONNECT BY PRIOR rn=rn-1 AND PRIOR name=name
/
楼上的有点复杂,我以前也是这么写