如何将两个无公共关联字段的按月分组SQL统计结果合并为一张表

方案1：UNION ALL合并后二次聚合（全数据库兼容，更推荐） #

直接将两张表的原始数据合并标记类型，再统一按月分组统计，无需额外关联逻辑，还能自动兼容仅在单表存在的月份（没有对应数据的字段自动返回0）：

SELECT
    DATE_FORMAT(month_date, '%M') AS `Month`,
    SUM(type = 'checkin') AS checkInCount,
    SUM(type = 'reserve') AS reserveCount
FROM (
    -- 合并两张表的日期数据，标记对应类型
    SELECT `date` AS month_date, 'checkin' AS type FROM check_in
    UNION ALL
    SELECT `date` AS month_date, 'reserve' AS type FROM reservation
) AS all_data
GROUP BY MONTH(month_date)
ORDER BY MONTH(month_date);

方案2：基于统计结果的关联查询 #

你需要的关联公共字段其实就是两个统计结果输出的Month字段，以此为关联键拼接两个统计结果即可。如果使用不支持FULL OUTER JOIN的数据库（比如MySQL），可以先提取所有月份再做左关联：

SELECT
    m.`Month`,
    IFNULL(t1.checkInCount, 0) AS checkInCount,
    IFNULL(t2.reserveCount, 0) AS reserveCount
FROM (
    -- 提取两张表所有出现过的月份，避免数据缺失
    SELECT DATE_FORMAT(`date`, '%M') AS `Month`, MONTH(`date`) AS month_num FROM check_in
    UNION
    SELECT DATE_FORMAT(`date`, '%M') AS `Month`, MONTH(`date`) AS month_num FROM reservation
) AS m
LEFT JOIN (
    -- 签到统计子查询
    SELECT DATE_FORMAT(check_in.date,'%M') AS `Month`, COUNT(check_in.Id) AS checkInCount 
    FROM check_in 
    GROUP BY MONTH(check_in.date)
) AS t1 ON m.`Month` = t1.`Month`
LEFT JOIN (
    -- 预订统计子查询
    SELECT DATE_FORMAT(reservation.date,'%M') AS `Month`, COUNT(reservation.id) AS reserveCount 
    FROM reservation
    GROUP BY MONTH(reservation.date)
) AS t2 ON m.`Month` = t2.`Month`
ORDER BY m.month_num;

如果使用PostgreSQL、SQL Server等支持全外连接的数据库，可以简化写法：

SELECT
    COALESCE(t1.`Month`, t2.`Month`) AS `Month`,
    COALESCE(t1.checkInCount, 0) AS checkInCount,
    COALESCE(t2.reserveCount, 0) AS reserveCount
FROM (
    SELECT DATE_FORMAT(check_in.date,'%M') AS `Month`, COUNT(check_in.Id) AS checkInCount 
    FROM check_in 
    GROUP BY MONTH(check_in.date)
) AS t1
FULL OUTER JOIN (
    SELECT DATE_FORMAT(reservation.date,'%M') AS `Month`, COUNT(reservation.id) AS reserveCount 
    FROM reservation
    GROUP BY MONTH(reservation.date)
) AS t2 ON t1.`Month` = t2.`Month`
ORDER BY MONTH(COALESCE(t1.`Month`, t2.`Month`));

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