关于sql的query，如何选择每个id的前n项？

如果是在sql 2005中的话:
with a as(
select id,产量,row_number() over(partition by id order by 产量 desc)
from 表)
select * from a where a<3

如果是sql 2000的话：
select * from (
select id,产量,(select count(*) from 表 as a2 where a2.id=a1.id and a2.产量>=a1.产量) as rank from
表 as a1) as b
where rank<3
order by id,n desc

SQL2000之前的版本不能处理并列的情况如：前几名的一样产量，这样的情况下用临时表新增一个标识列：select *,col=identity(int,1,1) from table order by ID asc,产量 desc

SQL05以上版本，row_number\rank\dense_rank排序函数，参照联机
或用cross apply处理
--> -->

if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[产量] int)
Insert #T
select 1,10 union all
select 1,20 union all
select 1,30 union all
select 3,70 union all
select 3,25 union all
select 3,40 union all
select 3,50
Go
select
t2.*
from
(Select distinct [id] from #