如何按月份分组并按年份拆分汇总MySQL表字段值？

这是个很常见的行转列+月度聚合需求，我给你提供两种实现方式（适配不同MySQL版本），同时拆解每个步骤的逻辑：

核心思路 #

1. 先构造12个月份的固定列表，确保结果一定返回12行（哪怕某年份对应月份无数据）
2. 从原表按「年份+月份」分组，计算每个组的fieldINT1和fieldINT2求和值
3. 通过CASE WHEN将不同年份的聚合结果转成列，空值用COALESCE替换为0

方案1：MySQL 8.0+ 支持CTE（更易读） #

WITH months AS (
    -- 构造12个月份的基础数据，保证结果必有12行
    SELECT 1 AS month_num, 'January' AS month_name UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
),
yearly_sums AS (
    -- 从原表按年份+月份聚合求和
    SELECT
        YEAR(fieldDate) AS year_val,
        MONTH(fieldDate) AS month_num,
        SUM(fieldINT1) AS sum_int1,
        SUM(fieldINT2) AS sum_int2  -- 注意原表列名是FiledINT2？如果是笔误请对应修改
    FROM your_table_name  -- 替换成你的实际表名
    WHERE YEAR(fieldDate) IN (2016, 2017, 2018)  -- 只过滤目标年份，提升效率
    GROUP BY year_val, month_num
)
SELECT
    m.month_name,
    -- 提取2016年的求和值，无数据则显示0
    COALESCE(MAX(CASE WHEN ys.year_val = 2016 THEN ys.sum_int1 END), 0) AS '2016_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2016 THEN ys.sum_int2 END), 0) AS '2016_sum_int2',
    -- 提取2017年的求和值
    COALESCE(MAX(CASE WHEN ys.year_val = 2017 THEN ys.sum_int1 END), 0) AS '2017_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2017 THEN ys.sum_int2 END), 0) AS '2017_sum_int2',
    -- 提取2018年的求和值
    COALESCE(MAX(CASE WHEN ys.year_val = 2018 THEN ys.sum_int1 END), 0) AS '2018_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2018 THEN ys.sum_int2 END), 0) AS '2018_sum_int2'
FROM months m
LEFT JOIN yearly_sums ys ON m.month_num = ys.month_num
GROUP BY m.month_num, m.month_name
ORDER BY m.month_num;  -- 按月份顺序排序

方案2：MySQL 5.x 兼容版（子查询替代CTE） #

如果你的MySQL版本低于8.0，不支持WITH语句，可以用子查询改写：

SELECT
    m.month_name,
    COALESCE(MAX(CASE WHEN ys.year_val = 2016 THEN ys.sum_int1 END), 0) AS '2016_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2016 THEN ys.sum_int2 END), 0) AS '2016_sum_int2',
    COALESCE(MAX(CASE WHEN ys.year_val = 2017 THEN ys.sum_int1 END), 0) AS '2017_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2017 THEN ys.sum_int2 END), 0) AS '2017_sum_int2',
    COALESCE(MAX(CASE WHEN ys.year_val = 2018 THEN ys.sum_int1 END), 0) AS '2018_sum_int1',
    COALESCE(MAX(CASE WHEN ys.year_val = 2018 THEN ys.sum_int2 END), 0) AS '2018_sum_int2'
FROM (
    SELECT 1 AS month_num, 'January' AS month_name UNION ALL
    SELECT 2, 'February' UNION ALL
    SELECT 3, 'March' UNION ALL
    SELECT 4, 'April' UNION ALL
    SELECT 5, 'May' UNION ALL
    SELECT 6, 'June' UNION ALL
    SELECT 7, 'July' UNION ALL
    SELECT 8, 'August' UNION ALL
    SELECT 9, 'September' UNION ALL
    SELECT 10, 'October' UNION ALL
    SELECT 11, 'November' UNION ALL
    SELECT 12, 'December'
) m
LEFT JOIN (
    SELECT
        YEAR(fieldDate) AS year_val,
        MONTH(fieldDate) AS month_num,
        SUM(fieldINT1) AS sum_int1,
        SUM(fieldINT2) AS sum_int2
    FROM your_table_name
    WHERE YEAR(fieldDate) IN (2016, 2017, 2018)
    GROUP BY year_val, month_num
) ys ON m.month_num = ys.month_num
GROUP BY m.month_num, m.month_name
ORDER BY m.month_num;

关键细节提醒 #

• 记得把your_table_name替换成你的实际表名
• 原表中的列名FiledINT2可能是笔误（应该是fieldINT2），如果实际表中是这个拼写，一定要在SQL里对应修改
• COALESCE(..., 0)的作用是把没有数据的月份的求和值显示为0，避免返回NULL
• 最终结果会按1-12月的顺序排列，符合常规的月度报表需求

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