【IT168 MSSQL开发】数据表：
    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，只能选两条记录
   
   
    --------------------------------------------------------------------------------
    作者：magicangel    时间：04-09-13 13:05
   
    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)
   
   
    --------------------------------------------------------------------------------
    作者：magicangel    时间：04-09-13 13:15
   
    也可以这么写：
   
    select * from magic a where (select count(1) from magic where dept=a.dept and qty>a.qty)<2
   
   
    --------------------------------------------------------------------------------
    作者：flywolf2000    时间：04-09-13 13:26
   
    看看我这个
    declare @linshi table (DEPT int ,ITEM char(10) ,QTY int)
    decllare @dept int
    set @dept=1
    while(@dept<=(select max(dept) from tablename ))
    begin
    insert into @linshi select top 2* from tablename order by qty where dept=@dept
    set @dept=@dept +1
    end
    select * from @linshi
   
   
    --------------------------------------------------------------------------------
    作者：magicangel    时间：04-09-13 13:45
   
   
    quote:
    --------------------------------------------------------------------------------
    最初由 flywolf2000 发布
    看看我这个
    declare @linshi table (DEPT int ,ITEM char(10) ,QTY int)
    decllare @dept int
    set @dept=1
    while(@dept<=(select max(dept) from tablename ))
    begin
    insert into @linshi select top 2* from tablename order by qty where dept=@dept
    set @dept=@dept +1
    end
    select * from @linshi
    --------------------------------------------------------------------------------
   
   
   
    嗯，跟我第一个答案的思路是一样的。不过有点小错误，我改一下，不要介意。
   
    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
    

    --------------------------------------------------------------------------------
    作者：sky721    时间：04-09-13 14:46
   
    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的执行效率有问题
   
   
    --------------------------------------------------------------------------------
    作者：sky721    时间：04-09-13 14:49
   
    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
    要倒入一个新表中，不想这样做
   
   
    --------------------------------------------------------------------------------
    作者：magicangel    时间：04-09-13 15:09
   
    对qty建索引，可以提高效率：
    create clustered index ix_magic on magic(qty)
   
   
    --------------------------------------------------------------------------------
    作者：seow163    时间：04-09-18 18:53
   
    select DEPT,ITEM,Max(QTY) as QTY
    into #Tmp
    from Table
    group by DEPT,ITEM
    select DEPT,ITEM,Max(QTY) as QTY
    from Table
    where DEPT+ITEM+Convert(varchar(10),QTY) not in(select DEPT+ITEM+Convert(varchar(10),QTY) from #Tmp)
    group by DEPT,ITEM
    union
    select DEPT,ITEM,QTY
    from #Tmp
    order by 1,2,3 desc
   
   
    --------------------------------------------------------------------------------
    作者：lodge    时间：04-09-18 19:58
   
    恩, 楼上的思路也挺好, 不过用临时表来做有点太费事了,
    select DEPT,ITEM,Max(QTY) as QTY
    from Table
    group by DEPT,ITEM
    UNION
    select DEPT,ITEM,Max(QTY) as QTY
    from Table T1
    Where exists(select null from table T2 where T1.dept=T2.dept and T2.qty > T1.qty)
    group by DEPT,ITEM
    如果还要求第三大第四大这个方法就不太适用了