17,377
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(
select 'A' t_name,'甲1' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲3' p_name from dual
)
select *
from (
select t_name,rank() over(partition by t_name order by p_name) rk
from t1)
where rk <= 2
t_name rk
--------------------
1 A 1
2 A 2
3 A 2
4 A 2
with t1 as
(
select 'A' t_name,'甲1' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲3' p_name from dual
union all
select 'B' t_name,'乙1' p_name from dual
union all
select 'B' t_name,'乙3' p_name from dual
union all
select 'B' t_name,'乙22' p_name from dual
)
select t_name,p_name
from
(
select t_name,p_name,
row_number() over(partition by t_name order by p_name) rn
from t1
)
where rn <= 2
t_name p_name
-------------------------
1 A 甲1
2 A 甲2
3 B 乙1
4 B 乙22