mysql内表和外表_内表查询用到外表

在 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