使用GROUP BY实现每行多聚合及GAMES表查询与结构优化咨询

一、基于现有表结构的查询实现 #

如果暂时不想调整表结构，完全可以通过UNION ALL拆分玩家参与记录，再结合GROUP BY实现聚合统计。核心思路是把每局游戏的两个玩家都拆成独立的参与记录，这样就能统一统计每个玩家的所有对局和获胜次数。

这里假设你的GAMES表有一个唯一标识每局游戏的game_id字段（如果没有的话，可以用数据库主键或临时生成行号），具体SQL语句如下（不同数据库的字符串拼接函数略有差异，我会标注清楚）：

SELECT 
    player_id,
    -- 拼接对局列表：格式为「对阵 X (结果: 获胜/失败)」，多个对局用分号分隔
    -- PostgreSQL/SQL Server 用 STRING_AGG
    STRING_AGG('对阵 ' || opponent || ' (结果: ' || CASE WHEN winner = player_id THEN '获胜' ELSE '失败' END || ')', '; ') AS 对局列表,
    -- MySQL 替换成 GROUP_CONCAT(...)
    -- GROUP_CONCAT('对阵 ', opponent, ' (结果: ', CASE WHEN winner = player_id THEN '获胜' ELSE '失败' END, ') SEPARATOR '; ') AS 对局列表,
    -- 统计获胜次数：只计数玩家作为winner的记录
    COUNT(CASE WHEN winner = player_id THEN 1 END) AS 获胜次数
FROM (
    -- 拆分出每个玩家的参与记录：先取player_1为当前玩家，player_2为对手
    SELECT 
        player_1 AS player_id, 
        player_2 AS opponent, 
        winner,
        game_id
    FROM GAMES
    UNION ALL
    -- 再取player_2为当前玩家，player_1为对手
    SELECT 
        player_2 AS player_id, 
        player_1 AS opponent, 
        winner,
        game_id
    FROM GAMES
) AS player_participation
GROUP BY player_id
ORDER BY player_id;

这段代码会输出每个玩家的ID、所有对局的详细列表，以及他的总获胜次数，完全满足你的需求。

二、表结构选择：现有结构 vs 规范化关联表 #

接下来聊聊要不要调整表结构，这取决于你的业务规模和长期需求：

1. 保留现有表结构（适合小型/快速迭代场景）

• 优点：无需额外建表，快速实现需求，适合数据量不大（比如几千/几万条对局记录）、不需要管理玩家额外属性（比如昵称、等级）的场景。
• 缺点：数据一致性难保证（比如玩家ID输错没法校验），后续如果要扩展玩家信息，需要频繁修改GAMES表；数据量过大时，UNION ALL的性能会有所下降。

2. 改用规范化关联表（适合中大型/长期维护场景）

如果你的业务有长期发展规划，建议调整为规范化结构：

• 创建PLAYERS表（存储玩家核心信息）：

  CREATE TABLE PLAYERS (
      player_id INT PRIMARY KEY,
      player_name VARCHAR(50) NOT NULL,
      -- 可添加其他属性：等级、注册时间等
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• 修改GAMES表（通过外键关联玩家表）：

  CREATE TABLE GAMES (
      game_id INT PRIMARY KEY AUTO_INCREMENT,
      player_1_id INT NOT NULL,
      player_2_id INT NOT NULL,
      winner_id INT, -- 允许NULL表示平局
      game_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      -- 添加外键约束，保证玩家ID合法
      FOREIGN KEY (player_1_id) REFERENCES PLAYERS(player_id),
      FOREIGN KEY (player_2_id) REFERENCES PLAYERS(player_id),
      FOREIGN KEY (winner_id) REFERENCES PLAYERS(player_id)
  );
  

  这种结构的优势：

  • 数据一致性：外键约束确保GAMES表中的玩家ID一定存在于PLAYERS表中，避免无效数据。
  • 扩展性：后续添加玩家属性（比如头像、段位）只需要修改PLAYERS表，不需要动GAMES表。
  • 查询灵活性：如果需要结合玩家昵称统计，直接关联PLAYERS表即可，不需要在GAMES表中重复存储昵称。

三、总结建议 #

• 如果只是临时需求或小型应用：直接用现有表结构，通过上面的SQL就能快速实现统计。
• 如果是长期维护的中大型应用：优先选择规范化关联表，虽然前期需要多建表，但后续的维护和扩展成本会低很多。

内容的提问来源于stack exchange，提问作者Carlos Alves Jorge