sql如何取每组中的前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
*/ 

转载于:https://www.cnblogs.com/xmxu/archive/2012/07/12/2588700.html