如何将两个按月分组统计的SELECT查询结果合并为并排展示

要实现将numeracao表和revisoes表的月度ID统计结果合并为一个结果集，展示1-12月的NUM（numeracao表统计数）和REV（revisoes表统计数），可以通过生成完整月份列表并左连接两个统计子查询的方式实现，确保每个月份都能被展示，即使对应表当月无数据。

完整SQL语句（支持CTE的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH months AS (
    SELECT 1 AS month 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
)
SELECT 
    m.month,
    COALESCE(n.NUM, 0) AS NUM,
    COALESCE(r.REV, 0) AS REV
FROM months m
LEFT JOIN (
    SELECT MONTH(data) AS month, COUNT(id) AS NUM
    FROM numeracao
    WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY MONTH(data)
) n ON m.month = n.month
LEFT JOIN (
    SELECT MONTH(dia) AS month, COUNT(id) AS REV
    FROM revisoes
    WHERE dia BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY MONTH(dia)
) r ON m.month = r.month
ORDER BY m.month;

兼容老版本数据库的写法（无CTE支持） #

SELECT 
    m.month,
    COALESCE(n.NUM, 0) AS NUM,
    COALESCE(r.REV, 0) AS REV
FROM (
    SELECT 1 AS month 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
) m
LEFT JOIN (
    SELECT MONTH(data) AS month, COUNT(id) AS NUM
    FROM numeracao
    WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY MONTH(data)
) n ON m.month = n.month
LEFT JOIN (
    SELECT MONTH(dia) AS month, COUNT(id) AS REV
    FROM revisoes
    WHERE dia BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY MONTH(dia)
) r ON m.month = r.month
ORDER BY m.month;

说明 #

1. 生成完整月份列表：通过UNION ALL生成1到12的月份，确保每个月份都出现在结果中，避免因某表当月无数据导致月份缺失。
2. 左连接统计结果：使用LEFT JOIN关联两个表的月度统计数据，保证即使某表当月无记录，仍能显示对应月份。
3. 处理空值：COALESCE函数将空值（无数据时的结果）转换为0，让结果更直观；若不需要转换，直接使用n.NUM和r.REV即可。

内容的提问来源于stack exchange，提问作者Eduardo Luís