MySQL按月份分组统计多日期列的课程各部分参与人数

可以通过CTE生成月份列表+分组统计各课程部分人数的方式实现，不需要创建单独的月份表。以下是适配MySQL的查询语句：

WITH months AS (
    SELECT 1 AS month_num, '一月' AS month_name UNION ALL
    SELECT 2, '二月' UNION ALL
    SELECT 3, '三月' UNION ALL
    SELECT 4, '四月' UNION ALL
    SELECT 5, '五月' UNION ALL
    SELECT 6, '六月' UNION ALL
    SELECT 7, '七月' UNION ALL
    SELECT 8, '八月' UNION ALL
    SELECT 9, '九月' UNION ALL
    SELECT 10, '十月' UNION ALL
    SELECT 11, '十一月' UNION ALL
    SELECT 12, '十二月'
),
part1_stats AS (
    SELECT 
        MONTH(part_1_date) AS month_num,
        COUNT(id) AS part_1_attendees
    FROM students
    WHERE part_1_date IS NOT NULL
    GROUP BY MONTH(part_1_date)
),
part2_stats AS (
    SELECT 
        MONTH(part_2_date) AS month_num,
        COUNT(id) AS part_2_attendees
    FROM students
    WHERE part_2_date IS NOT NULL
    GROUP BY MONTH(part_2_date)
),
part3_stats AS (
    SELECT 
        MONTH(part_3_date) AS month_num,
        COUNT(id) AS part_3_attendees
    FROM students
    WHERE part_3_date IS NOT NULL
    GROUP BY MONTH(part_3_date)
)
SELECT 
    m.month_name AS month,
    COALESCE(p1.part_1_attendees, 0) AS part_1_attendees,
    COALESCE(p2.part_2_attendees, 0) AS part_2_attendees,
    COALESCE(p3.part_3_attendees, 0) AS part_3_attendees
FROM months m
LEFT JOIN part1_stats p1 ON m.month_num = p1.month_num
LEFT JOIN part2_stats p2 ON m.month_num = p2.month_num
LEFT JOIN part3_stats p3 ON m.month_num = p3.month_num
ORDER BY m.month_num;

关键部分说明： #

1. CTE months：生成包含1-12月数字和对应中文名称的数据集，确保结果始终返回12行。
2. 各部分统计CTE：分别对part_1_date/part_2_date/part_3_date按月份分组统计人数，过滤掉空值（未报名的情况）。
3. 左连接+COALESCE：将月份表与三个统计结果左连接，保证每个月份都出现在结果中；用COALESCE把未匹配到的NULL值替换为0，符合需求的显示要求。
4. 排序：按月份数字排序，确保结果按1-12月顺序展示。

适配其他数据库的调整： #

• PostgreSQL：将MONTH()替换为EXTRACT(MONTH FROM part_1_date)。
• SQL Server：MONTH()函数可用，CTE语法一致。

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