MySQL实现各组最高得分玩家查询（含未参赛组）

Got it, let's work through this problem step by step. You need to pull the highest-scoring player as winner_id for each group—including groups where the player didn’t play any matches (like group 3’s player 40, who has a total score of 0). Here’s how to implement this in MySQL:

核心需求梳理 #

我们需要完成两个关键动作：

1. 计算所有玩家的总得分，从未参加比赛的玩家得分记为0；
2. 按组筛选出总得分最高的玩家作为该组的winner_id。

方法1：兼容所有MySQL版本（无窗口函数） #

如果你使用的是不支持窗口函数的旧版MySQL（8.0之前），可以用这个方案：

步骤1：统计所有玩家的总得分 #

先把玩家作为first_player和second_player的得分分别汇总，再补充上从未参赛的玩家（得分设为0）：

-- 统计所有玩家的总得分
SELECT player_id, SUM(score) AS total_score
FROM (
    -- 统计作为first_player的得分
    SELECT first_player AS player_id, first_score AS score
    FROM matches
    UNION ALL
    -- 统计作为second_player的得分
    SELECT second_player AS player_id, second_score AS score
    FROM matches
) AS all_match_scores
GROUP BY player_id
-- 补充从未参赛的玩家，得分设为0
UNION ALL
SELECT player_id, 0 AS total_score
FROM players
WHERE player_id NOT IN (
    SELECT first_player FROM matches UNION SELECT second_player FROM matches
);

步骤2：筛选每组得分最高的玩家 #

将上面的得分数据和players表关联，先算出每组的最高得分，再匹配回对应的玩家：

-- 最终查询语句（兼容所有MySQL版本）
SELECT 
    p.group_id,
    p.player_id AS winner_id
FROM players p
JOIN (
    -- 所有玩家的总得分数据
    SELECT player_id, SUM(score) AS total_score
    FROM (
        SELECT first_player AS player_id, first_score AS score FROM matches
        UNION ALL
        SELECT second_player AS player_id, second_score AS score FROM matches
    ) AS all_match_scores
    GROUP BY player_id
    UNION ALL
    SELECT player_id, 0 AS total_score
    FROM players
    WHERE player_id NOT IN (
        SELECT first_player FROM matches UNION SELECT second_player FROM matches
    )
) AS player_scores ON p.player_id = player_scores.player_id
JOIN (
    -- 计算每组的最高得分
    SELECT 
        p.group_id,
        MAX(player_scores.total_score) AS max_group_score
    FROM players p
    JOIN (
        SELECT player_id, SUM(score) AS total_score
        FROM (
            SELECT first_player AS player_id, first_score AS score FROM matches
            UNION ALL
            SELECT second_player AS player_id, second_score AS score FROM matches
        ) AS all_match_scores
        GROUP BY player_id
        UNION ALL
        SELECT player_id, 0 AS total_score
        FROM players
        WHERE player_id NOT IN (
            SELECT first_player FROM matches UNION SELECT second_player FROM matches
        )
    ) AS player_scores ON p.player_id = player_scores.player_id
    GROUP BY p.group_id
) AS group_max_scores 
    ON p.group_id = group_max_scores.group_id 
    AND player_scores.total_score = group_max_scores.max_group_score
ORDER BY p.group_id;

方法2：MySQL 8.0+ 简化版（窗口函数） #

如果你的MySQL版本是8.0及以上，窗口函数能让代码更简洁易读：

WITH player_total_scores AS (
    -- 计算每个玩家的总得分，未参赛玩家默认得0分
    SELECT 
        p.player_id,
        p.group_id,
        COALESCE(
            SUM(CASE
                WHEN m.first_player = p.player_id THEN m.first_score
                WHEN m.second_player = p.player_id THEN m.second_score
                ELSE 0
            END),
            0
        ) AS total_score
    FROM players p
    LEFT JOIN matches m ON p.player_id IN (m.first_player, m.second_player)
    GROUP BY p.player_id, p.group_id
),
ranked_players AS (
    -- 按组对玩家的得分降序排名
    SELECT 
        group_id,
        player_id,
        RANK() OVER (PARTITION BY group_id ORDER BY total_score DESC) AS score_rank
    FROM player_total_scores
)
-- 筛选每组排名第一的玩家（得分最高）
SELECT group_id, player_id AS winner_id
FROM ranked_players
WHERE score_rank = 1
ORDER BY group_id;

关键细节说明： #

• COALESCE函数确保未参赛玩家的得分不会出现NULL，而是被替换为0；
• RANK()窗口函数按组对玩家得分降序排名，得分最高的玩家会被标记为排名1。

预期输出 #

两种方案都会返回你需要的结果：

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