Mysql-------查询各科成绩前三名的记录

查询各科成绩前三名的记录

分析如下:

1 select s.* from (select distinct Student.*,a.c_id, a.s_score, count( distinct b.s_score)+1 as rank
2 from Score a  
3 left join  Score b  on a.c_id=b.c_id  and a.s_score<b.s_score
4 left join Student on(a.s_id=Student.s_id)
5 group by a.c_id, a.s_id
6 order by a.c_id, a.s_score desc) s
7 where s.rank between 1 and 3;