MySQL多表按天分组查询：如何获取任一表存在数据的统计结果

嘿，我懂你的困扰——内连接确实只会返回两个表当天都有数据的结果，而你需要覆盖所有有数据的日期，哪怕其中一个表当天没记录。虽然MySQL确实不支持FULL OUTER JOIN，但我们可以用个小技巧来实现需求：先获取所有出现过的日期集合，再分别左连接两个表统计。

解决思路 #

1. 获取所有唯一日期：把ride表和clicks表中的日期都提取出来，用UNION去重，得到所有有数据的日期列表。
2. 左连接统计：用这个日期列表分别左连接两个表，这样每个日期都会被保留，哪怕其中一个表当天没有匹配记录。
3. 正确统计数量：用COUNT(DISTINCT 字段)来统计，当没有匹配记录时，这个值会自动返回0（因为DISTINCT NULL不会计入统计）。

最终SQL代码 #

SELECT
    dates.day,
    COUNT(DISTINCT ride.id) AS ride_ads,
    COUNT(DISTINCT clicks.id) AS clicks
FROM (
    -- 生成所有有数据的日期集合
    SELECT EXTRACT(DAY FROM timestamp) AS day FROM ride
    UNION
    SELECT EXTRACT(DAY FROM time) AS day FROM clicks
) AS dates
-- 左连接ride表，保留所有日期
LEFT JOIN ride ON EXTRACT(DAY FROM ride.timestamp) = dates.day
-- 左连接clicks表，保留所有日期
LEFT JOIN clicks ON EXTRACT(DAY FROM clicks.time) = dates.day
-- 按日期分组统计
GROUP BY dates.day
-- 可选：按日期排序，让结果更直观
ORDER BY dates.day;

代码解释 #

• UNION会自动合并两个查询的结果并去重，确保我们得到所有出现过的日期，不管是在ride还是clicks表中。
• LEFT JOIN保证了即使ride或clicks表中某一天没有数据，对应的日期依然会出现在结果中，缺失的统计值会显示为0。
• COUNT(DISTINCT id)避免了同一日期内重复记录的干扰，同时正确处理了无匹配记录的情况（此时id为NULL，不会被计入统计）。

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