如何在MySQL 5.7中用SQL生成日期间隔填充数据

针对你的需求，以下是在MySQL 5.7中实现的SQL方案：

完整SQL代码 #

-- 初始化变量（可选，若起始日期有数据可省略）
SET @prev_num := 0;

SELECT 
    d.dates,
    @prev_num := CASE WHEN t.num IS NOT NULL THEN t.num ELSE @prev_num END AS num
FROM
    (SELECT 
        DATE_ADD('2022-02-01', INTERVAL n.n DAY) AS dates
    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 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
         SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30) AS n
    WHERE
        DATE_ADD('2022-02-01', INTERVAL n.n DAY) <= DATE_ADD('2022-02-01', INTERVAL 30 DAY)) AS d
LEFT JOIN t_test t ON d.dates = t.dates
ORDER BY d.dates;

实现说明 #

1. 生成连续日期：通过UNION ALL构造0到30的数字序列，结合DATE_ADD生成从2022-02-01到2022-03-03的所有连续日期。
2. 关联原表数据：将生成的连续日期表与t_test左连接，匹配对应日期的num值，缺失日期的num会显示为NULL。
3. 向前填充缺失值：使用变量@prev_num记录前一个非空的num值，当当前行num为NULL时，用变量值填充，实现缺失日期继承前一个存在的num值的效果。

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