要求查出每种课程成绩排行前5的学生名,课程名,分数. 知道的请帮帮忙~

Question

三张表
Student:id,name
Course:id,name
Score:id,sid,cid,score.
Mysql

Answer 1

你好，这个查询比较复杂，希望你能仔细研习，希望可以帮助到你。由于测试数据的数据量不大，我提供给你的这个例子中取得是排行前3的学生，你如果想测试排名前五的，加数据并且改一下SQL中的"rank<=5"即可。&nbsp;SQL查询语句如下：select&nbsp;s.name&nbsp;as&nbsp;student,c.name&nbsp;as&nbsp;course,score&nbsp;from&nbsp;(select&nbsp;sid,cid,score,rank&nbsp;from&nbsp;(select&nbsp;ff.sid,ff.cid,ff.score,if(@pcid=ff.cid,@rank:=@rank+1,@rank:=1)&nbsp;as&nbsp;rank,@pcid:=ff.cid&nbsp;from&nbsp;(select&nbsp;sid,cid,score&nbsp;from&nbsp;score&nbsp;order&nbsp;by&nbsp;cid&nbsp;asc,score&nbsp;desc)&nbsp;ff)&nbsp;result&nbsp;having&nbsp;rank<=3)&nbsp;aa&nbsp;inner&nbsp;join&nbsp;Student&nbsp;s&nbsp;on&nbsp;aa.sid=s.id&nbsp;inner&nbsp;join&nbsp;Course&nbsp;c&nbsp;on&nbsp;aa.cid=c.id;测试数据如图所示：执行的结果如下：+---------+--------+-------+|&nbsp;student&nbsp;|&nbsp;course&nbsp;|&nbsp;score&nbsp;|+---------+--------+-------+|&nbsp;s1&nbsp; &nbsp; &nbsp; |&nbsp;one&nbsp; &nbsp; |&nbsp; &nbsp; 80&nbsp;||&nbsp;s3&nbsp; &nbsp; &nbsp; |&nbsp;one&nbsp; &nbsp; |&nbsp; &nbsp; 56&nbsp;||&nbsp;s5&nbsp; &nbsp; &nbsp; |&nbsp;one&nbsp; &nbsp; |&nbsp; &nbsp; 45&nbsp;||&nbsp;s1&nbsp; &nbsp; &nbsp; |&nbsp;two&nbsp; &nbsp; |&nbsp; &nbsp; 94&nbsp;||&nbsp;s2&nbsp; &nbsp; &nbsp; |&nbsp;two&nbsp; &nbsp; |&nbsp; &nbsp; 84&nbsp;||&nbsp;s3&nbsp; &nbsp; &nbsp; |&nbsp;two&nbsp; &nbsp; |&nbsp; &nbsp; 77&nbsp;|+---------+--------+-------+6&nbsp;rows&nbsp;in&nbsp;set

Answer 2

select sname,cname,score from (select sname,cname,score, row_number() over(partition by cid order by score desc) "row" from(select student.name sname,course.name cname,score.score from score join student on score.sid = student.id join course on score.cid = course.id)) where "row" <=5