发表于: 2004.12.08 19:17
分类: dbms
出处: http://kyle.itpub.net/post/1626/9119
---------------------------------------------------------------
DEPT ITEM QTY
1 001 8
1 002 6
1 003 9
1 004 1
2 001 8
2 002 6
2 003 9
2 004 1
3 001 8
3 002 6
3 003 9
3 004 1
怎么样只选择每个DEPT里QTY排名前两名的ITEM
select top 2 * from table order by qty desc，只能选两条记录
----------------------------------------------------------------------------------------------
select 1 dept,'001' item,8 qty
into magic
union all
select 1,'002',6
union all
select 1,'003',9
union all
select 2,'001',8
union all
select 2,'002',6
union all
select 2,'003',9
select * from magic a where qty in (select top 2 qty from magic where dept=a.dept order by qty desc)
----------------------------------------------------------------------------------------------
--或者这样写
select * from magic a where (select count(1) from magic where dept=a.dept and qty>a.qty)<2
----------------------------------------------------------------------------------------------
--一个朋友的答案
declare @linshi table (DEPT int ,ITEM char(10) ,QTY int)
declare @dept int
set @dept=1
while(@dept<=(select max(dept) from magic ))
begin
insert into @linshi select top 2* from magic where dept=@dept order by qty desc
set @dept=@dept +1
end
select * from @linshi
