如何使用SQL为日期范围创建行数据

Question

表格如下:

---+------------+------------+---------
id | start_date | end_date   | amount |
---+------------+------------+---------
1 | 2021-01-01 | 2021-01-07 | 100    |
---+------------+------------+---------

我想把上面的表改成下面的

---+------------+------------+--------+---------------+
id | start_date | end_date   | amount | operation_date|
---+------------+------------+--------+---------------+
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-01    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-02    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-03    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-04    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-05    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-06    |
1 | 2021-01-01 | 2021-01-07 | 100    | 2021-01-07    |
---+------------+------------+--------+---------------+

我想根据开始日期和结束日期的范围将操作日期做成一行，并输入相同的信息。

你能帮忙吗?我已经找了很多，但是我找不到一个合适的答案。

Answer 1

我认为你可以这样使用RECURSIVE CTE:

WITH RECURSIVE cte AS (
SELECT id, start_date, end_date, amount, start_date AS operation_date 
FROM table1 
UNION ALL
SELECT id, start_date, end_date, amount, operation_date+INTERVAL 1 DAY 
FROM cte 
WHERE operation_date+INTERVAL 1 DAY <= end_date)
SELECT * 
FROM cte ;

operation_date2021-01-012021-01-022021-01-032021-01-042021-01-052021-01-062021-01-07