MySQL Pivoting 行转列实现：按assoc分组将多行player值转为多列

以下两种实现方式可适配不同版本的MySQL环境，将现有数据表转换为你需要的展示格式：

1. MySQL 8.0及以上版本（支持窗口函数） #

通过ROW_NUMBER()窗口函数给每个assoc分组内的player排序，再通过分组聚合提取对应列的值：

SELECT 
  assoc,
  MAX(CASE WHEN rn = 1 THEN player END) AS player1,
  MAX(CASE WHEN rn = 2 THEN player END) AS player2,
  MAX(CASE WHEN rn = 3 THEN player END) AS player3
FROM (
  SELECT 
    assoc,
    player,
    -- 按assoc分组，内部按player排序生成行号
    ROW_NUMBER() OVER (PARTITION BY assoc ORDER BY player) AS rn
  FROM 替换为你的实际表名
) AS temp_table
GROUP BY assoc;

如果需要调整player列的排列顺序，只需要修改ORDER BY后面的排序规则即可。

2. MySQL 5.x版本（无窗口函数支持） #

通过用户自定义变量实现分组编号逻辑，效果和窗口函数一致：

SELECT 
  assoc,
  MAX(CASE WHEN rn = 1 THEN player END) AS player1,
  MAX(CASE WHEN rn = 2 THEN player END) AS player2,
  MAX(CASE WHEN rn = 3 THEN player END) AS player3
FROM (
  SELECT 
    assoc,
    player,
    @row_num := IF(@current_assoc = assoc, @row_num + 1, 1) AS rn,
    @current_assoc := assoc
  FROM 替换为你的实际表名
  -- 初始化变量
  CROSS JOIN (SELECT @row_num := 0, @current_assoc := NULL) AS init_vars
  ORDER BY assoc, player
) AS temp_table
GROUP BY assoc;

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