MySQL动态透视表按日按销售状态汇总交易数报错求助

问题核心 #

尝试通过GROUP_CONCAT实现按日期、销售状态汇总交易数的动态透视表，但生成的SQL存在语法错误，导致执行失败。原代码的核心问题在于聚合逻辑错误、列名格式不规范、SQL拼接不完整。

错误点拆解 #

1. 聚合函数嵌套非法：MySQL不允许在MAX中嵌套COUNT，原逻辑混淆了行级判断与聚合统计的顺序
2. 列名格式错误：日期格式的列名（如2023-04-05）未用反引号包裹，会被解析为算术表达式报错
3. 引号转义错误：使用"转义引号不符合MySQL字符串语法，应直接使用单引号
4. SQL语句不完整：生成的字段列表缺少闭合逻辑，主SQL拼接后语法残缺

修正后的代码 #

SET @sql := NULL;

-- 动态拼接日期列的统计逻辑
SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN DATE(reserved_date) = ''', DATE(reserved_date), ''' THEN 1 ELSE 0 END) AS `', DATE(reserved_date), '`'
) ORDER BY reserved_date)
INTO @sql
FROM sales_reservation
WHERE agent_id NOT IN ('jorge','allan')
AND reservation_number != '';

-- 拼接完整的透视表SQL
SET @sql = CONCAT(
    'SELECT status, ', @sql, ' FROM sales_reservation 
     WHERE agent_id NOT IN (''jorge'',''allan'')
     AND reservation_number != ''''
     GROUP BY status'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键改动说明 #

• 聚合逻辑调整：用SUM(CASE...)替代嵌套聚合，直接统计当前状态在指定日期下的交易数
• 列名规范化：给日期列名添加反引号（`），避免MySQL将日期解析为表达式
• 引号处理：在字符串拼接中使用双单引号转义（两个单引号表示一个单引号），符合MySQL语法规范
• 条件复用：主SQL中重复过滤条件，避免统计不符合要求的数据
• 排序保证：GROUP_CONCAT中保留ORDER BY reserved_date，确保日期列按时间顺序排列

正确生成的SQL示例 #

SELECT status, 
SUM(CASE WHEN DATE(reserved_date) = '2023-04-05' THEN 1 ELSE 0 END) AS `2023-04-05`,
SUM(CASE WHEN DATE(reserved_date) = '2023-04-06' THEN 1 ELSE 0 END) AS `2023-04-06`,
SUM(CASE WHEN DATE(reserved_date) = '2023-04-07' THEN 1 ELSE 0 END) AS `2023-04-07`
FROM sales_reservation 
WHERE agent_id NOT IN ('jorge','allan')
AND reservation_number != ''
GROUP BY status

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