内表查询用到外表

    在 csdn 上看到的一个例子，很多记录中以某个字段为中心最前面的两条数据

 

--给个例子参考
--查询每门课程的前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 stuid
 from studentgrade as t2
 where t1.subid=t2.subid
         order by t2.grade desc)
order by subid, grade desc

--方法二：
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2

--方法三：
select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,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)
*/

共有三种方案，从难易程度上讲我倾向于后两种，从查询逻辑思想上来讲后两种是一样的

select * from StudentGrade t
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc