MySQL如何将游戏ID与玩家列表转换为列（固定玩家数场景）

问题场景 #

原始查询语句：

select match_id, player
from player_matches

查询结果（长表格式）：

match_id  player
1         messi
1         lebron
2         kobe
2         federer
3         serena
3         woods

期望转换为宽表格式（每个赛事对应固定数量的玩家列）：

match_id  player_1  player_2
1         messi     lebron
2         kobe      federer
3         serena    woods

需要实现的是：针对任意固定玩家数的场景（比如10位玩家对应11列：match_id + player_1到player_10），完成上述转置。

通用解决方案（支持所有SQL数据库） #

使用窗口函数生成玩家序号 + 条件聚合的方式，这是跨数据库通用的写法，无需依赖特定语法：

1. 先给每个赛事下的玩家分配一个序号（排序规则可按需调整，示例按玩家名称排序）
2. 通过MAX(CASE...)将不同序号的玩家映射到对应的列

以固定2个玩家为例，SQL语句如下：

SELECT 
    match_id,
    MAX(CASE WHEN player_seq = 1 THEN player END) AS player_1,
    MAX(CASE WHEN player_seq = 2 THEN player END) AS player_2
FROM (
    SELECT 
        match_id,
        player,
        -- 按赛事分组，给每个玩家分配序号
        ROW_NUMBER() OVER(PARTITION BY match_id ORDER BY player) AS player_seq
    FROM player_matches
) t
GROUP BY match_id;

如果是10个玩家的场景，只需扩展CASE语句到player_seq = 10，新增对应player_3到player_10的列即可。

特定数据库简化写法（支持PIVOT语法） #

如果使用SQL Server、Oracle等支持PIVOT语法的数据库，可以用更简洁的写法：

同样先生成玩家序号，再通过PIVOT转置：

SELECT 
    match_id,
    [1] AS player_1,
    [2] AS player_2,
    [3] AS player_3,
    -- 依次添加到[10] AS player_10
FROM (
    SELECT 
        match_id,
        player,
        ROW_NUMBER() OVER(PARTITION BY match_id ORDER BY player) AS player_seq
    FROM player_matches
) t
PIVOT (
    MAX(player) FOR player_seq IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) p;

关键说明 #

• 两种方法都依赖每个赛事的玩家数固定的前提，否则会出现部分列值为NULL（若某赛事玩家数不足）或数据丢失（若玩家数超过设定值）
• 窗口函数的ORDER BY可根据实际需求调整（比如按玩家加入时间排序），确保序号分配符合业务逻辑

内容的提问来源于stack exchange，提问作者NFeruch - FreePalestine