MySQL中基于日期值生成动态数据透视表列的实现方法

表结构与样例数据 #

表名：MED_TBL

MEDNAME      QUANTITY   SALES_DATE
XEPRAMYCIN      4       2024-10-27
FEVITAMOL       2       2024-10-27
XEPRAMYCIN      1       2024-10-26
FEVITAMOL       1       2024-10-26
XEPRAMYCIN      3       2024-10-25
FEVITAMOL       3       2024-10-25

静态透视表查询（参考） #

静态写法可正确生成指定日期列的透视表：

SELECT
  `MEDNAME`,
  SUM(CASE WHEN `SALES_DATE` = '2024-10-27' THEN `QUANTITY` ELSE 0 END) AS `2024-10-27`,
  SUM(CASE WHEN `SALES_DATE` = '2024-10-26' THEN `QUANTITY` ELSE 0 END) AS `2024-10-26`,
  SUM(CASE WHEN `SALES_DATE` = '2024-10-25' THEN `QUANTITY` ELSE 0 END) AS `2024-10-25`
FROM
  `MED_TBL`
GROUP BY
  `MEDNAME`;

动态SQL正确写法 #

你之前的动态SQL仅拼接了日期字符串，缺少静态写法里的SUM(CASE...)聚合逻辑，导致语法错误。正确写法需将每个日期转换成对应的聚合语句：

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'SUM(CASE WHEN `SALES_DATE` = ''', `SALES_DATE`, ''' THEN `QUANTITY` ELSE 0 END) AS `', `SALES_DATE`, '`'
  )
) INTO @sql
FROM `MED_TBL`;

SET @sql = CONCAT('SELECT `MEDNAME`, ', @sql, ' FROM `MED_TBL` GROUP BY `MEDNAME`');

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

关键说明 #

1. 用GROUP_CONCAT将每个唯一日期转换为对应的SUM(CASE)聚合语句，同时用反引号包裹日期列名避免语法冲突
2. 字符串拼接时需用双单引号转义单引号，确保SQL语法正确
3. 最终生成的SQL与静态写法逻辑完全一致，可自动适配表中所有存在的SALES_DATE值

执行结果 #

执行后将得到和静态查询一致的透视表结果，且自动包含所有新增日期列：

MEDNAME    2024-10-27  2024-10-26  2024-10-25
XEPRAMYCIN 4           1           3
FEVITAMOL  2           1           3

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