MySQL查询各科成绩前三名的记录及排名(不考虑成绩并列情况)
MySQL查询各科成绩前三名的记录及排名(不考虑成绩并列情况)
我的表结构:
先放代码
-- 查询各科成绩前三名的记录(不考虑成绩并列情况)
SELECT
sc1.`s_id`,
sc1.`c_id`,
sc1.`s_score`,
(SELECT
COUNT(*)
FROM
score sc3
WHERE
sc3.`c_id` = sc1.`c_id`
AND sc3.`s_score` > sc1.`s_score`
)+1 AS rank
FROM
score sc1
LEFT JOIN
score sc2
ON
sc1.`c_id` = sc2.`c_id`
AND sc1.`s_score` < sc2.`s_score`
GROUP BY
sc1.`c_id`,
sc1.`s_id`,
sc1.`s_score`
HAVING
COU