SQL如何将枚举category字段值转列并按日期分组统计数据

• 嵌套聚合函数违反SQL语法：不允许在MAX()中直接嵌套COUNT()这类聚合函数，嵌套聚合必须基于已分组的子查询结果操作
• 字段引用错误：d是日期维度子查询，没有category字段，你误将c.category写成了d.category
• 保留字冲突：delete是SQL保留关键字，直接作为列名会触发语法错误，需要用反引号包裹

方案1：基于你原有正确逻辑改造（兼容无数据日期补0） #

将你已经跑通的按分类+日期统计的逻辑作为子查询，再做行转列即可：

SELECT 
    date,
    MAX(CASE WHEN category = 'add' THEN total ELSE 0 END) AS `add`,
    MAX(CASE WHEN category = 'delete' THEN total ELSE 0 END) AS `delete`,
    MAX(CASE WHEN category = 'edit' THEN total ELSE 0 END) AS `edit`
FROM (
    SELECT c.category, 
        COUNT(l.id) total,  
        d.date as date
     FROM (SELECT DISTINCT category FROM log) c
     CROSS JOIN (SELECT DISTINCT DATE(date) date FROM log WHERE DATE(date) BETWEEN '2021-08-01' AND '2021-08-30') d
     LEFT JOIN log l ON l.category = c.category AND DATE(l.date) = d.date
     GROUP BY c.category, d.date
) t
GROUP BY date
ORDER BY date ASC

如果需要输出8/25/2021格式的日期，将子查询外的date替换为DATE_FORMAT(date, '%c/%e/%Y') AS date即可。

方案2：简化写法（无需额外关联维度表） #

如果不需要补全某类操作全量为0的日期记录，可直接通过条件计数实现，性能更优：

SELECT 
    DATE_FORMAT(date, '%c/%e/%Y') AS `date`,
    COUNT(CASE WHEN category = 'add' THEN 1 END) AS `add`,
    COUNT(CASE WHEN category = 'delete' THEN 1 END) AS `delete`,
    COUNT(CASE WHEN category = 'edit' THEN 1 END) AS `edit`
FROM log
WHERE DATE(date) BETWEEN '2021-08-01' AND '2021-08-30'
GROUP BY DATE(date)
ORDER BY DATE(date) ASC

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