取前几名的例子

 取前几名的例子
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--给个例子参考
--查询每门课程的前2名成绩
CREATE TABLE StudentGrade(
stuId CHAR(4),    --学号
subId INT,        --课程号
grade INT,        --成绩
PRIMARY KEY (stuId,subId)
)
GO
--表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78)
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87)
GO
/*
要查询每门课程的前2名成绩
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
如何实现？
*/
--查看数据
select * from StudentGrade
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--假如出现并列时，就取多个同学的话。
--方法一：
select distinct *
from StudentGrade as t1
where stuId in
 (select top 2 WITH TIES stuId
  from StudentGrade as t2
  where t1.subId=t2.subId
         order by t2.grade desc)
order by subId, grade desc
--方法二：
select * from StudentGrade t where (select count(distinct grade) from StudentGrade where subId=t.subId and grade>=t.grade)<=2
order by t.subId,t.grade desc
--方法三：
select * from StudentGrade t
where (select count(distinct grade) from StudentGrade where subId=t.subId and grade>t.grade)<=1
order by t.subId,t.grade desc
--结果
/*
stuId subId       grade      
----- ----------- -----------
001   1           97
003   1           93
003   2           95
002   2           80
004   3           87
003   3           85
(6 row(s) affected)
*/
drop table StudentGrade
 
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--注：方法一，由于手头没有MSSQL，还没有测试。方法二和方法三基本上是一样的，只是对大于和大于等于的处理不同。
--注2：对有分数相同的，也只取两个同学的方法，除了方法一里去掉WITH TIES外，还没找到其他的方法