SQL新手求助：用单条查询获取各游戏的冠亚军

核心思路 #

通过窗口函数对每个游戏内的玩家按得分排名，再用条件聚合（或T-SQL专属的PIVOT）将同游戏的冠亚军合并到一行，实现单条查询输出所有游戏的冠亚军结果。

假设表结构如下（若你的表字段不同，可自行调整关联条件）：

• players表：player_id（主键）、player_name
• games表：game_id、game_name、player_id、score（记录玩家在对应游戏的得分）

MySQL 实现 #

基础版（无并列，每个游戏仅1个冠亚军） #

SELECT 
    game_id,
    game_name,
    MAX(CASE WHEN rank_num = 1 THEN player_name END) AS first_place,
    MAX(CASE WHEN rank_num = 2 THEN player_name END) AS second_place
FROM (
    -- 子查询：给每个游戏内的玩家按得分降序排名
    SELECT 
        g.game_id,
        g.game_name,
        p.player_name,
        ROW_NUMBER() OVER (PARTITION BY g.game_id ORDER BY g.score DESC) AS rank_num
    FROM games g
    JOIN players p ON g.player_id = p.player_id
) ranked_players
WHERE rank_num <= 2
GROUP BY game_id, game_name
ORDER BY game_id;

支持并列版（同得分玩家共享名次） #

如果存在同得分玩家，用RANK()替代ROW_NUMBER()，并通过GROUP_CONCAT拼接并列玩家名字：

SELECT 
    game_id,
    game_name,
    GROUP_CONCAT(DISTINCT CASE WHEN rank_num = 1 THEN player_name END SEPARATOR ', ') AS first_place,
    GROUP_CONCAT(DISTINCT CASE WHEN rank_num = 2 THEN player_name END SEPARATOR ', ') AS second_place
FROM (
    SELECT 
        g.game_id,
        g.game_name,
        p.player_name,
        RANK() OVER (PARTITION BY g.game_id ORDER BY g.score DESC) AS rank_num
    FROM games g
    JOIN players p ON g.player_id = p.player_id
) ranked_players
WHERE rank_num <= 2
GROUP BY game_id, game_name
ORDER BY game_id;

T-SQL 实现 #

基础版（无并列） #

逻辑和MySQL一致，语法适配T-SQL：

SELECT 
    game_id,
    game_name,
    MAX(CASE WHEN rank_num = 1 THEN player_name END) AS first_place,
    MAX(CASE WHEN rank_num = 2 THEN player_name END) AS second_place
FROM (
    SELECT 
        g.game_id,
        g.game_name,
        p.player_name,
        ROW_NUMBER() OVER (PARTITION BY g.game_id ORDER BY g.score DESC) AS rank_num
    FROM games g
    JOIN players p ON g.player_id = p.player_id
) ranked_players
WHERE rank_num <= 2
GROUP BY game_id, game_name
ORDER BY game_id;

PIVOT 写法（T-SQL专属） #

用PIVOT函数直接转置排名结果：

SELECT 
    game_id,
    game_name,
    [1] AS first_place,
    [2] AS second_place
FROM (
    SELECT 
        g.game_id,
        g.game_name,
        p.player_name,
        ROW_NUMBER() OVER (PARTITION BY g.game_id ORDER BY g.score DESC) AS rank_num
    FROM games g
    JOIN players p ON g.player_id = p.player_id
) ranked_players
PIVOT (
    MAX(player_name)
    FOR rank_num IN ([1], [2])
) AS pivot_table
ORDER BY game_id;

支持并列版 #

用RANK()+STRING_AGG拼接并列玩家：

SELECT 
    game_id,
    game_name,
    STRING_AGG(CASE WHEN rank_num = 1 THEN player_name END, ', ') WITHIN GROUP (ORDER BY player_name) AS first_place,
    STRING_AGG(CASE WHEN rank_num = 2 THEN player_name END, ', ') WITHIN GROUP (ORDER BY player_name) AS second_place
FROM (
    SELECT 
        g.game_id,
        g.game_name,
        p.player_name,
        RANK() OVER (PARTITION BY g.game_id ORDER BY g.score DESC) AS rank_num
    FROM games g
    JOIN players p ON g.player_id = p.player_id
) ranked_players
WHERE rank_num <= 2
GROUP BY game_id, game_name
ORDER BY game_id;

关键语法说明 #

1. 窗口函数：PARTITION BY game_id按游戏分组，ORDER BY score DESC按得分降序排序，给每个游戏内的玩家生成排名。
   • ROW_NUMBER()：给每个玩家分配唯一排名（即使得分相同）
   • RANK()：同得分玩家共享排名（如两个第一，下一个是第三）
2. 条件聚合：通过CASE WHEN筛选出排名1、2的玩家，用MAX/GROUP_CONCAT/STRING_AGG将结果合并到对应列。

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