PHPMyAdmin多表查询：将同队玩家转为player1、player2列展示

原SQL存在的问题 #

• 关联条件错误：你用了tb_teams.team_id = tb_players.player_id，实际应该关联tb_teams.team_id = tb_players.player_teamid，这才是球员所属球队的正确关联逻辑
• 子查询返回多行：当一个球队有多名球员时，子查询会返回多个结果，无法直接作为单列值输出

解决方案 #

根据需求，要将同一球队的球员转为player1、player2这样的列，一行显示球队信息，以下是两种可行的SQL写法：

方法一：适用于MySQL 8.0+（支持窗口函数）

SELECT
    t.team_logo,
    t.team_name,
    t.team_abreviation,
    MAX(CASE WHEN p.rn = 1 THEN p.player_ign END) AS player1,
    MAX(CASE WHEN p.rn = 2 THEN p.player_ign END) AS player2
FROM tb_teams t
LEFT JOIN (
    -- 给每个球队的球员按player_id排序并编号
    SELECT
        player_teamid,
        player_ign,
        ROW_NUMBER() OVER(PARTITION BY player_teamid ORDER BY player_id) AS rn
    FROM tb_players
) p ON t.team_id = p.player_teamid
GROUP BY t.team_logo, t.team_name, t.team_abreviation;

• 子查询通过ROW_NUMBER()函数，按球队分组给每个球员分配序号（rn）
• 外层用MAX(CASE...)将不同序号的球员映射到对应的player1、player2列
• 使用LEFT JOIN确保没有球员的球队也能被查询出来，若只需要有球员的球队，换成INNER JOIN即可

方法二：适用于MySQL 5.x版本（不支持窗口函数）

SELECT
    t.team_logo,
    t.team_name,
    t.team_abreviation,
    MAX(CASE WHEN p.rn = 1 THEN p.player_ign END) AS player1,
    MAX(CASE WHEN p.rn = 2 THEN p.player_ign END) AS player2
FROM tb_teams t
LEFT JOIN (
    -- 用用户变量手动给每个球队的球员编号
    SELECT
        player_teamid,
        player_ign,
        @rn := IF(@current_team = player_teamid, @rn + 1, 1) AS rn,
        @current_team := player_teamid
    FROM tb_players, (SELECT @current_team := 0, @rn := 0) vars
    ORDER BY player_teamid, player_id
) p ON t.team_id = p.player_teamid
GROUP BY t.team_logo, t.team_name, t.team_abreviation;

• 通过用户变量@current_team记录当前球队，@rn记录球员序号，实现分组编号
• 后续逻辑和方法一一致，通过条件聚合转列

扩展说明 #

如果球队的球员数量超过2名，只需继续添加MAX(CASE WHEN p.rn = N THEN p.player_ign END) AS playerN的列即可，N对应球员的序号。

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