以下方法是使用了 Recursive CTE (Common Table Expressions). 而MySQL version 是 8.0, 如果 version < 8.0 是不能使用 WITH clause的.
What is CTE?
由於每條 statement 都會產生一個暫時的result set, 如果想賦予它一個名字給另外的 statement 使用, 就會用 CTE. 它是以WITH
為開頭.
WITH
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;
What is Recursive CTE?
是不斷 Loop 自己直到 terminating condition
fulfilled. 它是以WITH RECURSIVE
為開頭, 中間要使用 UNION ALL
.
WITH RECURSIVE
cte_name [(col1, col2, ...)]
AS
(
Selet a, b, ... n from table_name
UNION ALL
Select col1, col2, ...coln from cte_name
WHERE terminating condition
)
Select col1, col2, .. from cte_name;
How do get all dates from a date range?
利用 RECURSIVE CTE 產生一個table, 由start_date
開始不斷加一日產生一個Date record 直到到達end_date
(terminating condition).
set @start_date := '2022-01-01';
SET @end_date := '2022-01-05';
WITH RECURSIVE
DateRange(DateData) AS
(
SELECT @start_date
UNION ALL
SELECT DATE_ADD(DateData, INTERVAL 1 DAY) FROM DateRange WHERE DateData < @end_date
)
SELECT DateData FROM DateRange;
當中 DATE_ADD() function 是在 DateData 加一個 interval, 之後 returns the date.
Reference: