34,590
社区成员
发帖
与我相关
我的任务
分享
select * from (select *,rn=row_number()over(partition by col1 order by col2)a where rn in(1,2)
--或者
select * from tb t
where (select count(1) from tb where col1=t.col1 and col2<=t.col2)<=2
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col0] int,[col1] varchar(1),[col2] int)
insert [tb]
select 1,'a',1 union all
select 2,'a',2 union all
select 4,'a',3 union all
select 5,'c',1 union all
select 6,'c',2 union all
select 7,'c',3 union all
select 8,'b',1 union all
select 9,'b',2
go
select * from tb t
where col2 in(select top 2 col2 from tb where col1=t.col1 order by col2 asc)
/**
col0 col1 col2
----------- ---- -----------
1 a 1
2 a 2
5 c 1
6 c 2
8 b 1
9 b 2
(6 行受影响)
**/