19⭐

按各科成绩进行排序，并显示排名
**

SELECT 
    a.*,
  COUNT(b.s_score) + 1 rk
FROM 
    Score a
LEFT JOIN
    Score b
ON a.c_id=b.c_id AND a.s_score < b.s_score
GROUP BY a.c_id, a.s_id
ORDER BY a.c_id, a.s_score desc

也可以这样写

SELECT 
    a.*,
  (SELECT COUNT(s_score) FROM Score b WHERE a.c_id = b.c_id AND b.s_score > a.s_score) + 1  rk
FROM
    Score a
ORDER BY a.c_id, a.s_score desc

mysql 8.0版本以上可以用开窗函数

SELECT 
    a.*,
  rank() over(partition by c_id order by s_score desc) rk
from
    socre a