[sql]选择每组前N条记录

发布于 2019-04-14  29 次阅读


我们可以这样思考:

选择这样的记录:(与该条记录同组且在该条记录前)的数量小于N

样例

表结构

create table course
(
	cid int not null
		primary key,
	cname varchar(20) null
)
;

create table student
(
	sid int not null
		primary key,
	sname varchar(20) null
)
;

create table student_course
(
	sid varchar(20) not null,
	cid int not null,
	grade int null
)
;

sql语句

查询每门课成绩前3名的学生的学号,姓名,课程号,课程名,成绩

首先获取所有的学号,姓名,课程号,课程名,成绩

SELECT student.sid,student.sname,student_course.grade,course.cid,course.cname FROM student,course,student_course WHERE student_course.sid=student.sid and student_course.cid=course.cid

我们记上面这条语句为A,则sql语句可写成:

SELECT a.sid,a.sname,a.cid,a.cname,a.grade

FROM (A) a

WHERE

(SELECT count(DISTINCT b.grade) FROM (A) b WHERE b.cid=a.cid and b.grade>a.grade)<3

and a.grade is not NULL

ORDER by a.cid,a.grade DESC

最后得到的语句为:

SELECT a.sid,a.sname,a.cid,a.cname,a.grade FROM (SELECT student.sid,student.sname,student_course.grade,course.cid,course.cname FROM student,course,student_course WHERE student_course.sid=student.sid and student_course.cid=course.cid) a WHERE (SELECT count(DISTINCT b.grade) FROM (SELECT student.sid,student.sname,student_course.grade,course.cid,course.cname FROM student,course,student_course WHERE student_course.sid=student.sid and student_course.cid=course.cid) b WHERE b.cid=a.cid and b.grade>a.grade)<3 and a.grade is not NULL ORDER by a.cid,a.grade DESC