MySQL中如何外连接按不同条件分组的月度统计结果集？

嘿，这问题其实不难搞定！咱们的核心目标是把两个基于不同筛选条件的分组统计，按年月完整合并，确保所有出现过的年月都被保留，缺失的统计值显示为空或者NULL。下面是具体的实现方案：

完整SQL代码 #

WITH all_months AS (
    -- 获取过去3年内所有有记录的唯一年月，作为基准列表
    SELECT DISTINCT DATE_FORMAT(`Samples`.`created_at`, '%Y-%m') AS `Month of Created At`
    FROM `samples` AS `Samples`
    WHERE `Samples`.`created_at` BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 YEAR) AND CURRENT_TIMESTAMP
),
x_stats AS (
    -- 第一个查询的统计结果
    SELECT DATE_FORMAT(`Samples`.`created_at`, '%Y-%m') AS `Month of Created At`, COUNT(DISTINCT `Samples`.`id`) AS `Samples X`
    FROM `samples` AS `Samples`
    WHERE `Samples`.`group` = 'x' AND `Samples`.`created_at` BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 YEAR) AND CURRENT_TIMESTAMP
    GROUP BY DATE_FORMAT(`Samples`.`created_at`, '%Y-%m')
),
y_stats AS (
    -- 第二个查询的统计结果
    SELECT DATE_FORMAT(`Samples`.`created_at`, '%Y-%m') AS `Month of Created At`, COUNT(DISTINCT `Samples`.`id`) AS `Samples Y`
    FROM `samples` AS `Samples`
    WHERE `Samples`.`section` = 'y' AND `Samples`.`created_at` BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 YEAR) AND CURRENT_TIMESTAMP
    GROUP BY DATE_FORMAT(`Samples`.`created_at`, '%Y-%m')
)
SELECT
    am.`Month of Created At`,
    IFNULL(xs.`Samples X`, '') AS `Samples X`,
    IFNULL(ys.`Samples Y`, '') AS `Samples Y`
FROM all_months am
LEFT JOIN x_stats xs ON am.`Month of Created At` = xs.`Month of Created At`
LEFT JOIN y_stats ys ON am.`Month of Created At` = ys.`Month of Created At`
ORDER BY am.`Month of Created At` ASC;

代码解释 #

1. all_months 子查询：先从samples表中提取过去3年内所有存在记录的年月，生成一个完整的年月基准列表，这一步是为了保证不会漏掉任何有数据的月份。
2. x_stats 和 y_stats 子查询：分别对应你原来的两个分组统计查询，这里补加了GROUP BY（其实你原来的查询里也需要加上，不然在SQL严格模式下会报错哦）。
3. 左连接合并：以all_months为基础，分别左连接两个统计结果，这样即使某个月份只有其中一组有数据，另一组也会显示为空（用IFNULL把NULL转成空字符串，你也可以改成0，如果需要数字类型的话）。

最终输出示例 #

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