MySQL查询透视改造：按tipo拆分月度统计列需求

核心思路 #

利用MySQL的**条件聚合（CASE WHEN + 聚合函数）**实现行转列，将每个月份下不同tipo的统计值拆分为单独列，同时保留递归CTE生成的完整月度序列，确保图表数据的连续性。

完整实现代码 #

假设你的递归CTE用于生成月度时间范围，关联progetti、items_header、item_type_catalog统计各类型数据，改造后的完整查询如下：

WITH RECURSIVE cte_periods AS (
    -- 递归生成需要统计的月度范围（示例：从项目最早创建时间到当前月份）
    SELECT DATE_FORMAT(MIN(pr.created_at), '%Y-%m-01') AS period_start 
    FROM progetti pr
    UNION ALL
    SELECT DATE_ADD(period_start, INTERVAL 1 MONTH) 
    FROM cte_periods 
    WHERE period_start < DATE_FORMAT(CURDATE(), '%Y-%m-01')
),
cte_monthly_data AS (
    -- 统计每个月度、每个tipo的总数值（保留原有逻辑，优化空值处理）
    SELECT
        DATE_FORMAT(p.period_start, '%Y-%m') AS period,
        COALESCE(SUM(ih.amount), 0) AS total,
        COALESCE(itc.tipo, 0) AS tipo
    FROM cte_periods p
    LEFT JOIN progetti pr 
        ON DATE_FORMAT(pr.created_at, '%Y-%m') = DATE_FORMAT(p.period_start, '%Y-%m')
    LEFT JOIN items_header ih 
        ON pr.id = ih.progetto_id
    LEFT JOIN item_type_catalog itc 
        ON ih.tipo_id = itc.id
    GROUP BY period, tipo
)
-- 透视转换：每个月份一行，各tipo单独列
SELECT
    period,
    SUM(CASE WHEN tipo = 1 THEN total ELSE 0 END) AS `tipo_1`,
    SUM(CASE WHEN tipo = 2 THEN total ELSE 0 END) AS `tipo_2`,
    SUM(CASE WHEN tipo = 3 THEN total ELSE 0 END) AS `tipo_3`,
    SUM(CASE WHEN tipo = 4 THEN total ELSE 0 END) AS `tipo_4`
FROM cte_monthly_data
GROUP BY period
ORDER BY period;

关键细节说明 #

• 递归CTE优化：将月度起始日期统一格式化为YYYY-MM-01，避免日期格式不一致导致的关联错误，确保月度范围连续。
• 空值处理：用COALESCE将NULL值替换为0，保证即使某个月份无对应类型数据，图表仍能拿到0值，避免数据断层。
• 条件聚合逻辑：通过CASE WHEN匹配tipo值，结合SUM聚合（因每个period+tipo仅一行，用MAX也可，SUM兼容性更强），将行数据转为列数据。
• 排序：最后按period排序，保证输出的月度顺序符合时间逻辑，直接适配Chart.js的x轴顺序。

扩展说明 #

如果tipo的取值不是固定1-4，而是从item_type_catalog动态获取的，可使用动态SQL生成透视列：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN tipo = ', tipo, ' THEN total ELSE 0 END) AS `tipo_', tipo, '`')
) INTO @sql
FROM item_type_catalog;

SET @sql = CONCAT('
WITH RECURSIVE cte_periods AS (
    SELECT DATE_FORMAT(MIN(pr.created_at), ''%Y-%m-01'') AS period_start 
    FROM progetti pr
    UNION ALL
    SELECT DATE_ADD(period_start, INTERVAL 1 MONTH) 
    FROM cte_periods 
    WHERE period_start < DATE_FORMAT(CURDATE(), ''%Y-%m-01'')
),
cte_monthly_data AS (
    SELECT
        DATE_FORMAT(p.period_start, ''%Y-%m'') AS period,
        COALESCE(SUM(ih.amount), 0) AS total,
        COALESCE(itc.tipo, 0) AS tipo
    FROM cte_periods p
    LEFT JOIN progetti pr 
        ON DATE_FORMAT(pr.created_at, ''%Y-%m'') = DATE_FORMAT(p.period_start, ''%Y-%m'')
    LEFT JOIN items_header ih 
        ON pr.id = ih.progetto_id
    LEFT JOIN item_type_catalog itc 
        ON ih.tipo_id = itc.id
    GROUP BY period, tipo
)
SELECT period, ', @sql, ' FROM cte_monthly_data GROUP BY period ORDER BY period;
');

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

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