如何在MySQL查询中补全无数据的缺失日期？

这个问题很常见——当数据库中某天没有对应记录时，GROUP BY会自动跳过这些日期。要补全缺失日期并将对应统计数显示为0，我们需要先生成完整的目标日期范围，再和你的统计结果做左连接，具体方案分两种场景：

方案一：MySQL 8.0+（支持递归CTE） #

递归CTE是生成连续日期最简洁的方式，代码如下：

WITH RECURSIVE date_range AS (
    -- 起始日期：今天往前推10天
    SELECT SUBDATE(CURDATE(), 10) AS day
    UNION ALL
    -- 递归生成后续日期，直到今天
    SELECT ADDDATE(day, 1)
    FROM date_range
    WHERE day < CURDATE()
)
-- 左连接日期范围和你的统计结果，用COALESCE把NULL转为0
SELECT dr.day, COALESCE(gd.total, 0) AS total
FROM date_range dr
LEFT JOIN (
    -- 你的原统计逻辑
    SELECT DATE(timestamp) AS day, COUNT(id) AS total
    FROM goals_data
    WHERE company = 1 
      AND DATE(timestamp) BETWEEN SUBDATE(CURDATE(), 10) AND CURDATE()
    GROUP BY day
) gd ON dr.day = gd.day
ORDER BY dr.day DESC;

方案二：MySQL 5.x（不支持CTE） #

如果你的MySQL版本较低，可以用临时数字表生成日期范围：

SELECT dates.day, COALESCE(gd.total, 0) AS total
FROM (
    -- 生成0到10的数字，对应往前推0到10天的日期
    SELECT SUBDATE(CURDATE(), n) AS day
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 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
    ) numbers
) dates
LEFT JOIN (
    -- 你的原统计逻辑
    SELECT DATE(timestamp) AS day, COUNT(id) AS total
    FROM goals_data
    WHERE company = 1 
      AND DATE(timestamp) BETWEEN SUBDATE(CURDATE(), 10) AND CURDATE()
    GROUP BY day
) gd ON dates.day = gd.day
ORDER BY dates.day DESC;

核心逻辑说明 #

1. 生成完整日期范围：不管数据库有没有记录，先确保我们有目标时间段内的所有日期；
2. 左连接统计结果：用左连接保留所有日期，没有匹配到统计数据的日期会返回NULL；
3. 替换NULL为0：用COALESCE()函数把NULL转换成0，满足你对缺失日期显示0的需求。

这样执行后，你就能得到包含2018-04-06在内的所有日期，对应total为0的结果了。

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