mysql - 为日期范围内的每一天创建一行？

Question

我有一个这样的表:

+----+---------+------------+
| id |  price  |    date    |
+----+---------+------------+
| 1  | 340     | 2018-09-02 |
| 2  | 325     | 2018-09-05 |
| 3  | 358     | 2018-09-08 |
+----+---------+------------+

我需要制作一个每天都有一行的 View 。像这样:

+----+---------+------------+
| id |  price  |    date    |
+----+---------+------------+
| 1  | 340     | 2018-09-02 |
| 1  | 340     | 2018-09-03 |
| 1  | 340     | 2018-09-04 |
| 2  | 325     | 2018-09-05 |
| 2  | 325     | 2018-09-06 |
| 2  | 325     | 2018-09-07 |
| 3  | 358     | 2018-09-08 |
+----+---------+------------+

我可以使用带有循环 (foreach) 的 PHP 并创建一个临时变量来保存之前的价格，直到有一个新的日期。

但我需要创建一个 View ......所以我应该使用纯 SQL 来实现......知道我该怎么做吗？

Answer 1

您可以使用递归 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