MySQL 5.7实现无外键两表按日期分组合并（缺失日期补0）

既然MySQL 5.7不支持FULL OUTER JOIN，我们可以通过先收集所有日期维度，再分别左连接两个统计结果的方式来实现需求，核心思路是用UNION ALL获取所有可能的日期，再基于这些日期去匹配两个表的统计数据，缺失的部分用IFNULL填充为0。

完整SQL代码（使用CTE，更易读） #

-- 先获取所有涉及的日期，来自两个表的有效数据日期
WITH all_dates AS (
    SELECT DATE(added) AS day
    FROM user_subscription
    WHERE topic_id = 39
    UNION ALL
    SELECT DATE(usq.date) AS day
    FROM user_submitted_q usq
    JOIN questions q ON usq.question_id = q.id
    WHERE q.topic_id = 39
),
-- 去重得到唯一日期列表
unique_dates AS (
    SELECT DISTINCT day FROM all_dates
),
-- 主题订阅数统计（原查询1）
topic_sub_stats AS (
    SELECT DATE(added) AS day, COUNT(*) AS TopicSub
    FROM user_subscription
    WHERE topic_id = 39
    GROUP BY DATE(added)
),
-- 问题提交数统计（原查询2）
q_sub_stats AS (
    SELECT DATE(usq.date) AS day, COUNT(*) AS QSub
    FROM user_submitted_q usq
    JOIN questions q ON usq.question_id = q.id
    WHERE q.topic_id = 39
    GROUP BY DATE(usq.date)
)
-- 左连接填充缺失值为0
SELECT 
    ud.day AS `Date`,
    IFNULL(qss.QSub, 0) AS QSub,
    IFNULL(tss.TopicSub, 0) AS TopicSub
FROM unique_dates ud
LEFT JOIN q_sub_stats qss ON ud.day = qss.day
LEFT JOIN topic_sub_stats tss ON ud.day = tss.day
ORDER BY ud.day;

代码解释 #

1. all_dates CTE：收集两个表中所有符合条件的原始日期（包含重复项），确保不会漏掉任何一个有数据的日期。
2. unique_dates CTE：对日期去重，得到唯一的日期维度列表，作为最终结果的基础框架。
3. 两个统计CTE：直接复用你原本的两个查询，单独提取后让代码逻辑更清晰。
4. 最终查询：以唯一日期列表为核心，分别左连接两个统计结果，用IFNULL()把未匹配到的统计值转为0，最后按日期排序保证结果有序。

兼容子查询版本（若MySQL 5.7未开启CTE） #

如果你的环境不支持CTE，可以换成子查询写法，功能完全一致：

SELECT 
    ud.day AS `Date`,
    IFNULL(qss.QSub, 0) AS QSub,
    IFNULL(tss.TopicSub, 0) AS TopicSub
FROM (
    SELECT DISTINCT day FROM (
        SELECT DATE(added) AS day
        FROM user_subscription
        WHERE topic_id = 39
        UNION ALL
        SELECT DATE(usq.date) AS day
        FROM user_submitted_q usq
        JOIN questions q ON usq.question_id = q.id
        WHERE q.topic_id = 39
    ) AS all_dates
) AS ud
LEFT JOIN (
    SELECT DATE(added) AS day, COUNT(*) AS TopicSub
    FROM user_subscription
    WHERE topic_id = 39
    GROUP BY DATE(added)
) AS tss ON ud.day = tss.day
LEFT JOIN (
    SELECT DATE(usq.date) AS day, COUNT(*) AS QSub
    FROM user_submitted_q usq
    JOIN questions q ON usq.question_id = q.id
    WHERE q.topic_id = 39
    GROUP BY DATE(usq.date)
) AS qss ON ud.day = qss.day
ORDER BY ud.day;

这个方案完全适配MySQL 5.7，能完美实现你想要的结果——所有出现过的日期都会显示，没有对应数据的列自动填充0。

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