使用MySQL实现行转列重组——基于团队出勤登记数据表

要把你的登记数据表（假设表名为register）的行数据重组为列，MySQL没有内置的PIVOT函数，但我们可以用CASE WHEN结合聚合函数来实现，下面分两种常见场景给出方案：

场景1：按团队、运动员分组，将不同日期的出勤状态转为列 #

如果想让每个运动员的每条记录合并为一行，把各日期的出勤状态作为单独列，用以下SQL：

SELECT 
    id_team,
    id_athlete,
    MAX(CASE WHEN date = '2018-04-19' THEN id_presence END) AS presence_20180419,
    MAX(CASE WHEN date = '2018-04-20' THEN id_presence END) AS presence_20180420
FROM 
    register
GROUP BY 
    id_team, id_athlete
ORDER BY 
    id_team, id_athlete;

说明： #

• CASE WHEN会匹配对应日期，取出该日期的id_presence值，非匹配日期返回NULL
• 用MAX()聚合函数是因为每个运动员每天只有一条记录，聚合后能保留有效数值、过滤NULL
• 分组依据id_team和id_athlete确保每个运动员在每个团队下只有一行结果

场景2：按日期、团队分组，将不同运动员的出勤状态转为列 #

如果想按日期和团队展示，把每个运动员的出勤作为列，用以下SQL：

SELECT 
    date,
    id_team,
    MAX(CASE WHEN id_athlete = 4 THEN id_presence END) AS athlete_4,
    MAX(CASE WHEN id_athlete = 5 THEN id_presence END) AS athlete_5,
    MAX(CASE WHEN id_athlete = 6 THEN id_presence END) AS athlete_6,
    MAX(CASE WHEN id_athlete = 14 THEN id_presence END) AS athlete_14,
    MAX(CASE WHEN id_athlete = 15 THEN id_presence END) AS athlete_15,
    MAX(CASE WHEN id_athlete = 16 THEN id_presence END) AS athlete_16
FROM 
    register
GROUP BY 
    date, id_team
ORDER BY 
    date, id_team;

动态生成列（适配未知/动态的日期或运动员） #

如果你的日期或运动员数量不固定，不想硬编码列名，可以用动态SQL自动生成列：

动态生成日期列 #

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN date = ''',
            date,
            ''' THEN id_presence END) AS presence_',
            REPLACE(date, '-', '')
        )
    ) INTO @sql
FROM register;

SET @sql = CONCAT('SELECT id_team, id_athlete, ', @sql, ' FROM register GROUP BY id_team, id_athlete ORDER BY id_team, id_athlete');

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

动态生成运动员列 #

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN id_athlete = ',
            id_athlete,
            ' THEN id_presence END) AS athlete_',
            id_athlete
        )
    ) INTO @sql
FROM register;

SET @sql = CONCAT('SELECT date, id_team, ', @sql, ' FROM register GROUP BY date, id_team ORDER BY date, id_team');

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

注意事项 #

• 如果GROUP_CONCAT生成的SQL过长，可能需要调整MySQL的group_concat_max_len参数来支持更长的字符串
• 动态SQL需要有足够的权限执行PREPARE和EXECUTE语句

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