with t1 as
(select 'num1' id, 'aaa' col1, 'aaa1' col2, 'aaa2' col3
from dual
union all
select 'num1' id, 'bbb' col1, 'bbb1' col2, 'bbb2' col3
from dual
union all
select 'num1' id, 'ccc' col1, 'ccc1' col2, 'ccc2' col3
from dual
union all
select 'num2' id, 'ddd' col1, 'ddd1' col2, 'ddd2' col3
from dual
union all
select 'num2' id, 'eee' col1, 'eee1' col2, 'eee2' col3
from dual
union all
select 'num3' id, 'fff' col1, 'fff1' col2, 'fff2' col3
from dual
union all
select 'num3' id, 'ggg' col1, 'ggg1' col2, 'ggg2' col3
from dual
union all
select 'num4' id, 'hhh' col1, 'hhh1' col2, 'hhh2' col3 from dual)
select t2.id, t2.col1, t2.col2, t2.col3
from (select t.id, count(t.id) cnt from t1 t group by t.id) t1,
(select id,
col1,
col2,
col3,
row_number() over(partition by t.id order by t.id) rnum
from t1 t) t2
where t1.cnt = t2.rnum
and t1.id = t2.id
order by t2.id;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货