如何用MySQL GROUP BY将分组数据转为命名列？保龄球赛事结果展示

嘿，这个场景我在做保龄球计分工具的时候刚好碰到过！你现在需要把行式的单局得分数据转成列式的球员汇总数据——也就是大家常说的Pivot（行转列）操作，因为局数不固定，所以得用动态的方式来处理，不能写死列名。

先给你看最终要实现的目标格式（对应你给的示例数据）：

下面分两种常用场景给你具体实现方案：

方案一：数据库层面动态行转列（推荐，性能更优） #

不同数据库的动态Pivot写法略有差异，这里给你列几个主流数据库的实现：

1. SQL Server 版本

-- 第一步：动态生成所有局数对应的列名（game_1、game_2...）
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME('game_' + CAST(game AS VARCHAR(10))) 
                    FROM (SELECT DISTINCT game FROM your_table) t
                    ORDER BY game
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- 第二步：构建Pivot查询并执行
SET @query = N'SELECT MIN(id) AS id, fixture_id, member_id, team_id, ' + @cols + N' 
            FROM 
            (
                SELECT id, fixture_id, member_id, team_id,
                    ''game_'' + CAST(game AS VARCHAR(10)) AS game_col,
                    score
                FROM your_table
            ) x
            PIVOT 
            (
                MAX(score)
                FOR game_col IN (' + @cols + N')
            ) p 
            GROUP BY fixture_id, member_id, team_id, ' + @cols + N'
            ORDER BY member_id'

EXEC sp_executesql @query

注：用MIN(id)是为了取每个球员的第一条记录ID，MAX(score)是因为每个球员单局只会有一个得分，聚合函数不影响结果。

2. MySQL 版本

MySQL没有原生Pivot函数，用CASE WHEN+动态SQL实现：

-- 第一步：生成动态列的聚合语句
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN game = ', game, ' THEN score END) AS game_', game
    )
  ) INTO @sql
FROM your_table;

-- 第二步：构建完整查询并执行
SET @sql = CONCAT('SELECT MIN(id) AS id, fixture_id, member_id, team_id, ', @sql, ' 
                  FROM your_table 
                  GROUP BY fixture_id, member_id, team_id 
                  ORDER BY member_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案二：应用层代码处理（适合不想写复杂SQL的场景） #

如果你的应用用Python/Java等语言开发，也可以先查询所有原始数据，再在代码里做转换。比如用Python的Pandas：

import pandas as pd

# 假设从数据库查询到的原始数据存在df中
df = pd.DataFrame([
    [1,1,1,1,1,189],
    [2,1,1,1,2,167],
    [3,1,1,1,3,190],
    [4,1,2,2,1,100],
    [5,1,2,2,2,167],
    [6,1,2,2,3,158]
], columns=['id','fixture_id','member_id','team_id','game','score'])

# 行转列
df_pivot = df.pivot_table(
    index=['fixture_id', 'member_id', 'team_id'],
    columns='game',
    values='score',
    aggfunc='first'
).reset_index()

# 重命名列名
df_pivot.columns = ['fixture_id', 'member_id', 'team_id'] + [f'game_{g}' for g in df_pivot.columns[3:]]
# 补充id列（取每个球员的第一条记录ID）
df_pivot = df.merge(df_pivot, on=['fixture_id','member_id','team_id']).drop_duplicates(subset=['member_id'])[['id','fixture_id','member_id','team_id','game_1','game_2','game_3']]

print(df_pivot)

额外注意点 #

• 如果有的球员局数少于最大局数，转列后对应位置会显示NULL，你可以用COALESCE(score, 0)（SQL）或者fillna(0)（Python）把空值替换成0；
• 如果存在同一个球员同一局多次提交得分的情况，要根据业务逻辑选择用MAX/MIN/AVG聚合，或者先做去重处理。

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