求SQL查询：统计拥有最多最大胜利分差的玩家信息

完全可以用单条SQL查询搞定这个需求，咱们分阶段拆解逻辑，最后整合为一个完整语句：

步骤1：先筛选有效记录并计算分差 #

首先只保留玩家得分高于bot的记录，同时计算胜利分差player_score - bot_score，这是后续判断“最大胜利分差”的基础：

SELECT 
    player_name,
    bot_name,
    player_score - bot_score AS win_diff
FROM game_scores
WHERE player_score > bot_score

步骤2：找出每个bot的最高分差值 #

针对每个bot，计算它对应的最大胜利分差值，这样就能定位到哪些玩家拿到了该bot的最高分差：

SELECT 
    bot_name,
    MAX(player_score - bot_score) AS max_win_diff
FROM game_scores
WHERE player_score > bot_score
GROUP BY bot_name

步骤3：关联得到每个bot的最大分差玩家 #

把前两步的结果关联起来，就能得到每个bot对应的、拿到最大胜利分差的玩家（如果有多个玩家并列最高分差，都会被保留）：

SELECT 
    gs.player_name,
    gs.bot_name
FROM game_scores gs
JOIN (
    SELECT 
        bot_name,
        MAX(player_score - bot_score) AS max_win_diff
    FROM game_scores
    WHERE player_score > bot_score
    GROUP BY bot_name
) bot_max ON gs.bot_name = bot_max.bot_name 
    AND (gs.player_score - gs.bot_score) = bot_max.max_win_diff
WHERE gs.player_score > bot_score

步骤4：统计次数并找出最多的玩家 #

最后对玩家分组统计次数，筛选出次数最多的结果：

SELECT 
    player_name,
    COUNT(*) AS max_win_count
FROM (
    SELECT 
        gs.player_name
    FROM game_scores gs
    JOIN (
        SELECT 
            bot_name,
            MAX(player_score - bot_score) AS max_win_diff
        FROM game_scores
        WHERE player_score > bot_score
        GROUP BY bot_name
    ) bot_max ON gs.bot_name = bot_max.bot_name 
        AND (gs.player_score - gs.bot_score) = bot_max.max_win_diff
    WHERE gs.player_score > bot_score
) player_max_wins
GROUP BY player_name
ORDER BY max_win_count DESC
LIMIT 1;

验证示例数据 #

把你提供的示例数据代入这个查询，会得到：

完全符合预期结果。如果存在多个玩家并列次数最多的情况，LIMIT 1只会返回第一个，要是想返回所有并列玩家，可以用窗口函数优化：

WITH player_max_counts AS (
    SELECT 
        player_name,
        COUNT(*) AS max_win_count,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
    FROM (
        SELECT 
            gs.player_name
        FROM game_scores gs
        JOIN (
            SELECT 
                bot_name,
                MAX(player_score - bot_score) AS max_win_diff
            FROM game_scores
            WHERE player_score > bot_score
            GROUP BY bot_name
        ) bot_max ON gs.bot_name = bot_max.bot_name 
            AND (gs.player_score - gs.bot_score) = bot_max.max_win_diff
        WHERE gs.player_score > bot_score
    ) player_max_wins
    GROUP BY player_name
)
SELECT player_name, max_win_count
FROM player_max_counts
WHERE rank = 1;

这个版本会返回所有拥有最多最大胜利分差次数的玩家，逻辑更严谨。

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