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 
    COUNT(sc2.`s_id`) <= 2
ORDER BY
    sc1.`c_id`,
    sc1.`s_score` DESC

思路：
首先是创建相同的sc1和sc2，根据sc1.c_id左外连接在一起，并且条件是

FROM
    score sc1
LEFT JOIN
    score sc2
ON
    sc1.`c_id` = sc2.`c_id`
    AND sc1.`s_score` < sc2.`s_score`
-- 意思是在sc2找出相同课程但分数比sc1更高的行

这时脑海里应该有这么个图了（脑海里排序之后）：

可以看到右边sc2列的都是分数大于sc1的情况
然后就可以进行三次分组,

GROUP BY
    sc1.`c_id`,
    sc1.`s_id`,
    sc1.`s_score`

三次分组之后就是得到上面那个图的每个红色框框缩为一行的情况

然后就应该只能sc2中允许有两个以下的人比sc1高对吧，如果有3个比sc1高那就是第四名了，所以分组是有条件的

HAVING 
    COUNT(sc2.`s_id`) <= 2

好，到目前为止我们已经找出了每科前三的各行数据了，接下来排序整理就可以得到最终结果了

ORDER BY
    sc1.`c_id`,
    sc1.`s_score` DESC

最终结果：

另外，如果要增加排名的话，可以在查询结果集添加

(SELECT
    COUNT(*) 
FROM 
    score sc3
WHERE
    sc3.`c_id` = sc1.`c_id`
    AND sc3.`s_score` > sc1.`s_score`
        )+1  AS rank

找出比同课程sc1.s_score高分的人数+1就是排名了，最终结果：