实现按a字段分组后，在组内再按照b字段排序，之后每组取Top n条数据

来源：互联网发布：万方数据库怎么收费编辑：程序博客网时间：2024/04/30 13:22

题目：

S表（Sid,Sname）为学生表,C表(Cid,Cname,Cteacher)为课程表,SC表(Sid,Cid,Scgrade)为选课关系表。其中，Sid为学号，Sname为姓名，Cid为课程号，Cname为课程名，Cteacher为任课教师，Scgrade为学生成绩。请用SQL语言实现：列出每门课程成绩最好的两位学生的课程名称，姓名，成绩。

-----------------------------方法一
select * from
(select * from
sc as a where (select Count(*) from sc as b where a.cid=b.cid and b.scgrade>=a.scgrade) <=2 ) as aa,s,c where s.sid=aa.sid and c.cid =aa.cid order by aa.cid

-----------------------------方法二
select * from
(select *,(select count(*) from sc as b where a.cid = b.cid and b.scgrade >= a.scgrade) as counts from sc as a ) as a1,s as a2,c as a3 where a1.sid=a2.sid and a1.cid=a3.cid and a1.counts<=2 order by a1.cid

----------------------------------------------------------