SQL Server 获取每组中的前N个

方法一：

--建立测试环境 
create table ta3(id varchar(4),repairvalue numeric(4,2),pieceid varchar(2),facid varchar(8)) 
insert into ta3 
select '01',4.6,'1','aaa' 
union all select '02',2,'1','aaa' 
union all select '03',0,'1','aaa' 
union all select '04',3,'1','aaa' 
union all select '05',5,'1','aaa' 
union all select '06',4.6,'2','aaa' 
union all select '07',7.8,'2','aaa' 
union all select '08',0,'2','aaa' 
union all select '09',5,'2','aaa' 
union all select '10',3,'2','aaa' 
--测试 
Select id,repairvalue,pieceid from ta3 A 
Where Not Exists(Select 1 from ta3 Where pieceid=A.pieceid And repairvalue>A.repairvalue Having Count(1)>2) --这里N取3
Order By pieceid,repairvalue Desc 
--删除测试环境 
Drop Table ta3 
--结果 
/* 
id repairvalue pieceid 
05 5.00 1 
01 4.60 1 
04 3.00 1 
07 7.80 2 
09 5.00 2 
06 4.60 2 
*/ 

方法二：

if object_id('tb') is not null
   drop table tb
go


create table tb
([id] int, [title] numeric(12,1), [typeid] int, [datetime] int)


insert into tb
select 1,1.1,1,1
union all select 2,1.2,1,2
union all select 3,1.3,1,3
union all select 4,2.1,2,4
union all select 5,2.2,2,5
union all select 6,2.3,2,6
union all select 7,3.1,3,7
union all select 8,3.2,3,8
union all select 9,3.3,3,9


select *
from
(
select *,
       
       --先按typeid分组,在一组中按照datetime降序排列,来编号
       ROW_NUMBER() over(partition by typeid 
                             order by datetime desc)  as rownum
from tb
)t
where rownum<=2  --取行号为1和2的,也就是时间最大的2条数据


/*
id<span style="white-space:pre">	</span>title<span style="white-space:pre">	</span>typeid<span style="white-space:pre">	</span>datetime<span style="white-space:pre">	</span>rownum
3<span style="white-space:pre">	</span>1.3<span style="white-space:pre">	</span>1<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>1
2<span style="white-space:pre">	</span>1.2<span style="white-space:pre">	</span>1<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>2
6<span style="white-space:pre">	</span>2.3<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>6<span style="white-space:pre">	</span>1
5<span style="white-space:pre">	</span>2.2<span style="white-space:pre">	</span>2<span style="white-space:pre">	</span>5<span style="white-space:pre">	</span>2
9<span style="white-space:pre">	</span>3.3<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>9<span style="white-space:pre">	</span>1
8<span style="white-space:pre">	</span>3.2<span style="white-space:pre">	</span>3<span style="white-space:pre">	</span>8<span style="white-space:pre">	</span>2
*/