求每组前2名,你有几种方法?(sql server2000+sql server2005)-CSDN博客

create table abc(
i nvarchar(10),
ii int,
iii int,
iiii int,
price money)
go
insert into abc
select 'b',1,2,1,11
union all
select 'b',211,2,1,211
union all
select 'a',21,2,1,311
union all
select 'd',41,42,1,411
union all
select 'd',41,42,1,511
union all
select 'd',41,42,1,611
union all
select 'e',1,2,1,11
union all
select 'e',71,2,1,31
union all
select 'e',61,2,1,911
union all
select 'e',771,2,1,1
go

要求的结果是：
以i分组，求每组price最大的前2条记录

i          ii          iii         iiii        price
---------- ----------- ----------- ----------- ---------------------
a          21          2           1           311.0000
b          1           2           1           11.0000
b          211         2           1           211.0000
d          41          42          1           511.0000
d          41          42          1           611.0000
e          71          2           1           31.0000
e          61          2           1           911.0000

1.
select *
from abc a
where (
select count(*) from abc b
where a.i=b.i and b.price>a.price)<2
order by i,price

连接查询，判断数量

2.
select i,ii,iii,iiii,price
from (
select (select isnull(sum(1),0)+1 from abc b where a.i=b.i and a.price<b.price) ids,*
from abc a) tem
where ids<3
order by i,price

生产一个内部表，给内部表tem中的每一组排序，并把排序号放入新列ids中

3.
declare @looptime int
declare @count int
declare @i nvarchar(10)
/*定义表变量@abc，和表ABC中的所有列类型相同*/
declare @abc table(
i nvarchar(10),
ii int,
iii int,
iiii int,
price money)
declare @tem table(
ids int identity,
class nvarchar(10))
/*把表ABC中的所有组全部查询出来，暂时存在表变量@tem中*/
insert into @tem(class)
select i
  from abc
  group by i
/*求出表变量@tem中行数量*/
select @count=@@rowcount
/*循环变量@looptime赋初值＝1*/
select @looptime=1
while(@looptime<=@count)
begin
/*将每组名赋值到变量@i*/
select @i=class
  from @tem
  where ids=@looptime
/*将每组前2名插入到表变量@abc中*/
insert into @abc
  select top 2 *
   from abc
   where i=@i
   order by price desc
/*循环变量@looptime累加1*/
select @looptime=@looptime+1
end
/*显示结果*/
select *
from @abc
order by i,price

4.
用游标来处理
方法和我的第3种方法类似，大家可以自己试试

5.利用sql server2005排名函数
SELECT *
FROM    ( SELECT    i ,
                    ii ,
                    iii ,
                    iiii ,
                    price ,
                    de = ROW_NUMBER() OVER ( PARTITION BY i ORDER BY [price] DESC )
          FROM      abc
        ) AS bb
WHERE   de <= 2

我共写了5种,1到4都可以在sql2000下运行,5必须在sql2005运行

不知道大家还有什么其他好方法，可以交流，谢谢。

我的系列文章