MySQL获取全年各月份统计数据（含无记录月份）

要返回2023年所有月份的统计数据（包括无记录的月份），核心是先构建一个包含全年12个月的数据集，再通过左连接关联bookings表，确保所有月份都能被展示。以下是两种场景的实现方案：

场景1：获取每个月份所有国籍的统计数据 #

该方案会展示每个月份下所有国籍的记录数和金额总和，无记录的月份/国籍组合会显示统计数为0：

WITH months AS (
    SELECT 1 AS MonthNumber, 'January' AS MonthName, 2023 AS YearNumber UNION ALL
    SELECT 2, 'February', 2023 UNION ALL
    SELECT 3, 'March', 2023 UNION ALL
    SELECT 4, 'April', 2023 UNION ALL
    SELECT 5, 'May', 2023 UNION ALL
    SELECT 6, 'June', 2023 UNION ALL
    SELECT 7, 'July', 2023 UNION ALL
    SELECT 8, 'August', 2023 UNION ALL
    SELECT 9, 'September', 2023 UNION ALL
    SELECT 10, 'October', 2023 UNION ALL
    SELECT 11, 'November', 2023 UNION ALL
    SELECT 12, 'December', 2023
)
SELECT 
    COALESCE(b.nationality, '无数据') AS nationality,
    m.MonthName,
    COUNT(b.booking_id) AS dataCount,
    m.MonthNumber,
    m.YearNumber,
    IFNULL(SUM(b.amount), 0.00) AS amount
FROM months m
LEFT JOIN bookings b 
    ON MONTH(b.booked_date) = m.MonthNumber 
    AND YEAR(b.booked_date) = m.YearNumber
GROUP BY m.MonthNumber, m.MonthName, m.YearNumber, b.nationality
ORDER BY m.MonthNumber, dataCount DESC;

场景2：获取每个月份记录数最高的国籍统计 #

如果需要和原SQL逻辑一致，返回每个月份数据量最高的一条记录（包括无记录的月份），可以用窗口函数实现：

WITH months AS (
    SELECT 1 AS MonthNumber, 'January' AS MonthName, 2023 AS YearNumber UNION ALL
    SELECT 2, 'February', 2023 UNION ALL
    SELECT 3, 'March', 2023 UNION ALL
    SELECT 4, 'April', 2023 UNION ALL
    SELECT 5, 'May', 2023 UNION ALL
    SELECT 6, 'June', 2023 UNION ALL
    SELECT 7, 'July', 2023 UNION ALL
    SELECT 8, 'August', 2023 UNION ALL
    SELECT 9, 'September', 2023 UNION ALL
    SELECT 10, 'October', 2023 UNION ALL
    SELECT 11, 'November', 2023 UNION ALL
    SELECT 12, 'December', 2023
),
monthly_stats AS (
    SELECT 
        COALESCE(b.nationality, '无数据') AS nationality,
        m.MonthName,
        COUNT(b.booking_id) AS dataCount,
        m.MonthNumber,
        m.YearNumber,
        IFNULL(SUM(b.amount), 0.00) AS amount,
        ROW_NUMBER() OVER (PARTITION BY m.MonthNumber ORDER BY COUNT(b.booking_id) DESC) AS rn
    FROM months m
    LEFT JOIN bookings b 
        ON MONTH(b.booked_date) = m.MonthNumber 
        AND YEAR(b.booked_date) = m.YearNumber
    GROUP BY m.MonthNumber, m.MonthName, m.YearNumber, b.nationality
)
SELECT nationality, MonthName, dataCount, MonthNumber, YearNumber, amount
FROM monthly_stats
WHERE rn = 1
ORDER BY MonthNumber;

关键说明 #

1. 月份数据集构建：用WITH子句生成2023年12个月份的固定数据，确保所有月份都被覆盖。
2. 左连接的作用：左连接months和bookings表，保证即使bookings中无对应月份的记录，月份信息依然会被保留。
3. 空值处理：
   • COUNT(b.booking_id)：避免无记录时统计数错误（COUNT(*)会统计月份表的行，导致结果为1）。
   • IFNULL(SUM(b.amount), 0.00)：将无记录时的金额总和转为0，避免显示NULL。
   • COALESCE(b.nationality, '无数据')：给无记录的月份的国籍字段一个明确标识。
4. 过滤逻辑调整：移除原SQL中指定月份的WHERE条件，改为在连接条件中匹配月份和年份，避免过滤掉无数据的月份行。

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