【MySQL】各科の成績別に並べ替え、ランキング表示

各科の成績別に並べ替え、ランキングを表示する

select s_id,a.c_id,c_name,s_score,rank from (
select s_id,c_id,s_score,
case 
	when c_id = @cid then 
		if(@prevRank = s_score,@curRank,@curRank := @curRank + 1)
	else 
		@curRank := 1
end as rank,
@cid := c_id,
@prevRank := s_score
from score, (select @cid:=null,@prevRank:=null,@curRank:=0) p
order by c_id,s_score desc) a left join course on a.c_id=course.c_id