如何实现MySQL动态Pivot表：自动适配新增员工补贴类型

由于MySQL不支持原生动态Pivot功能，要实现根据动态变化的补贴类型自动生成列的需求，你可以通过动态SQL+预处理语句或者存储过程来实现，以下是具体步骤和示例代码：

方法一：使用动态SQL与预处理语句 #

步骤1：生成动态列的SQL片段 #

先从表中提取所有唯一的Description值，拼接成对应的CASE语句片段，作为Pivot的列：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Description = ''',
      Description,
      ''' THEN Amount ELSE 0 END) AS `',
      Description, '`'
    )
  ) INTO @sql
FROM your_table_name; -- 替换为你的实际表名

这段代码会生成类似如下的片段：
MAX(CASE WHEN Description = 'Allowance 1' THEN Amount ELSE 0 END) AS Allowance 1, MAX(CASE WHEN Description = 'Allowance 2' THEN Amount ELSE 0 END) AS Allowance 2``

步骤2：拼接完整的Pivot查询语句 #

将上述片段拼接到主查询中：

SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM your_table_name GROUP BY ID');

步骤3：执行预处理语句 #

通过预处理语句执行动态生成的SQL：

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

执行后即可得到目标动态Pivot结果，新增补贴类型后重新执行这段代码，就能自动识别并新增对应列。

方法二：封装为存储过程 #

如果需要重复使用该逻辑，可以将上述步骤封装为存储过程：

DELIMITER //
CREATE PROCEDURE dynamic_pivot_subsidies()
BEGIN
  SET @sql = NULL;
  SELECT
    GROUP_CONCAT(DISTINCT
      CONCAT(
        'MAX(CASE WHEN Description = ''',
        Description,
        ''' THEN Amount ELSE 0 END) AS `',
        Description, '`'
      )
    ) INTO @sql
  FROM your_table_name;

  SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM your_table_name GROUP BY ID');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用时只需调用：CALL dynamic_pivot_subsidies();即可。

注意事项 #

• GROUP_CONCAT存在默认长度限制，若补贴类型数量较多，需调整参数：SET SESSION group_concat_max_len = 1000000;
• 如果Description包含单引号等特殊字符，需用QUOTE()函数处理转义，避免SQL语法错误：Description = ', QUOTE(Description), '

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