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

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          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

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.

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

posted @ 2004-08-23 11:55  aierong  阅读(2576)  评论(11编辑  收藏