是否在日期范围内为每天创建一行?

您可以使用递归CTE在“间隙”中生成记录。为了避免最后一个日期之后出现无限的空白,首先在源数据中获取最大日期,并确保在递归中不绕过该日期。

我给你的桌子打过电话 tbl

with recursive cte as (
    select     id, 
               price, 
               date, 
               (select max(date) date from tbl) mx
    from       tbl
    union all
    select     cte.id,
               cte.price, 
               date_add(cte.date, interval 1 day),
               cte.mx
    from       cte
    left join  tbl
           on  tbl.date = date_add(cte.date, interval 1 day)
    where      tbl.id is null
           and cte.date <> cte.mx
)
select   id, 
         price, 
         date
from     cte
order by 3;

demo with mysql 8

这是一种不需要分析函数就可以工作的方法。此答案使用日历表联接方法。下面的第一个CTE是查询其余部分所基于的基表。我们使用相关子查询查找CTE中最新的日期 早于当前日期,该日期具有非 NULL 价格。这是找出 id 和 price 值应适用于日历表中不出现在原始数据集中的日期。

WITH cte AS (    
    SELECT cal.date, t.price, t.id
    FROM
    (
        SELECT '2018-09-02' AS date UNION ALL
        SELECT '2018-09-03' UNION ALL
        SELECT '2018-09-04' UNION ALL
        SELECT '2018-09-05' UNION ALL
        SELECT '2018-09-06' UNION ALL
        SELECT '2018-09-07' UNION ALL
        SELECT '2018-09-08'
    ) cal
    LEFT JOIN yourTable t
        ON cal.date = t.date
),
cte2 AS (
    SELECT
        t1.date,
        t1.price,
        t1.id,
        (SELECT MAX(t2.date) FROM cte t2
         WHERE t2.date <= t1.date AND t2.price IS NOT NULL) AS nearest_date
    FROM cte t1
)

SELECT
    (SELECT t2.id FROM yourTable t2 WHERE t2.date = t1.nearest_date) id,
    (SELECT t2.price FROM yourTable t2 WHERE t2.date = t1.nearest_date) price,
    t1.date
FROM cte2 t1
ORDER BY
    t1.date;