存储过程编写求助：如何给排名前二的班级分别追加分数

要实现给排名前二的班级追加分数的操作，你需要在现有查询逻辑的基础上，增加获取班级排名和批量更新分数的步骤。以下是两种适配不同MySQL版本的实现方案：

方案一：适配MySQL 8.0+（支持窗口函数） #

CREATE PROCEDURE total_scores()
SQL SECURITY DEFINER
BEGIN
    -- 1. 汇总并展示所有班级的初始总得分（按分数降序）
    SELECT 
        class,
        SUM(scores) AS 'total scores'
    FROM players
    GROUP BY class
    ORDER BY SUM(scores) DESC;

    -- 2. 用CTE获取班级排名，筛选前两名
    WITH class_ranks AS (
        SELECT 
            class,
            RANK() OVER (ORDER BY SUM(scores) DESC) AS rank_num
        FROM players
        GROUP BY class
    )
    -- 3. 给对应班级的玩家加分：第一名加2，第二名加1
    UPDATE players p
    JOIN class_ranks cr ON p.class = cr.class
    SET p.scores = p.scores + CASE cr.rank_num WHEN 1 THEN 2 WHEN 2 THEN 1 END
    WHERE cr.rank_num <= 2;

    -- 可选：展示加分后的班级总得分，验证结果
    SELECT 
        class,
        SUM(scores) AS 'total scores after bonus'
    FROM players
    GROUP BY class
    ORDER BY SUM(scores) DESC;
END //

方案二：适配MySQL 5.x（不支持窗口函数） #

用变量模拟排名逻辑：

CREATE PROCEDURE total_scores()
SQL SECURITY DEFINER
BEGIN
    -- 1. 汇总并展示所有班级的初始总得分（按分数降序）
    SELECT 
        class,
        SUM(scores) AS 'total scores'
    FROM players
    GROUP BY class
    ORDER BY SUM(scores) DESC;

    -- 2. 创建临时表存储前两名班级及排名
    CREATE TEMPORARY TABLE IF NOT EXISTS top_classes AS
    SELECT 
        class,
        @rank := @rank + 1 AS rank_num
    FROM (
        SELECT class
        FROM players
        GROUP BY class
        ORDER BY SUM(scores) DESC
        LIMIT 2
    ) AS ranked_classes,
    (SELECT @rank := 0) AS init_rank;

    -- 3. 给对应班级的玩家加分
    UPDATE players p
    JOIN top_classes tc ON p.class = tc.class
    SET p.scores = p.scores + CASE tc.rank_num WHEN 1 THEN 2 WHEN 2 THEN 1 END;

    -- 可选：展示加分后的班级总得分
    SELECT 
        class,
        SUM(scores) AS 'total scores after bonus'
    FROM players
    GROUP BY class
    ORDER BY SUM(scores) DESC;
END //

关键逻辑说明 #

• 先执行初始汇总查询，满足“按总得分排序展示班级”的需求；
• 通过CTE或临时表获取班级排名，筛选出前两名；
• 利用JOIN关联玩家表和排名数据，通过CASE语句区分加分规则，批量更新对应班级的玩家分数；
• 临时表会在存储过程执行结束后自动销毁，不会产生冗余数据。

内容的提问来源于stack exchange，提问作者Ben