The table looks like this:
--- ------------ ------------ ---------
id | start_date | end_date | amount |
--- ------------ ------------ ---------
1 | 2021-01-01 | 2021-01-07 | 100 |
--- ------------ ------------ ---------
I want to change the table above to the following.
--- ------------ ------------ -------- ---------------
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 |
--- ------------ ------------ -------- ---------------
I want to make the operation date a row according to the range of start date and end date and input the same information.
Can you help? I've done a lot of searching, but I can't find a suitable answer.
CodePudding user response：
I think you can use RECURSIVE CTE like:
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 ;
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