SELECT
DATE_FORMAT ( DATE_ADD ( '${satrtDate}', INTERVAL seq.seq DAY ), '%Y-%m-%d' ) AS DATE,
FROM
(
SELECT
( t2.i + t1.i * 10 + t0.i * 100 ) AS seq
FROM
(
SELECT
0 AS i UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5 UNION ALL
SELECT
6 UNION ALL
SELECT
7 UNION ALL
SELECT
8 UNION ALL
SELECT
9
) AS t0,
(
SELECT
0 AS i UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5 UNION ALL
SELECT
6 UNION ALL
SELECT
7 UNION ALL
SELECT
8 UNION ALL
SELECT
9
) AS t1,
(
SELECT
0 AS i UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5 UNION ALL
SELECT
6 UNION ALL
SELECT
7 UNION ALL
SELECT
8 UNION ALL
SELECT
9
) AS t2
) AS seq
WHERE
seq.seq <= DATEDIFF( '${endDate}', '${satrtDate}' )
ORDER BY
seq.seq
方式二:创建视图
CREATE VIEW v_digits AS
SELECT 0 AS digit UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9;
CREATE VIEW v_numbers AS
SELECT
ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM
v_digits as ones,
v_digits as tens,
v_digits as hundreds,
v_digits as thousands;
-- 生成的日期格式为 yyyy-MM-dd
CREATE VIEW v_dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
v_numbers
UNION ALL
SELECT
ADDDATE(CURRENT_DATE(), number + 1) AS date
FROM
v_numbers;
-- 生成的日期格式为 yyyy-MM
CREATE VIEW v_months AS
SELECT
DATE_FORMAT(SUBDATE(CURRENT_DATE(), INTERVAL number MONTH),'%Y-%m') AS date
FROM
v_numbers
UNION ALL
SELECT
DATE_FORMAT(ADDDATE(CURRENT_DATE(), INTERVAL number+1 MONTH),"%Y-%m") AS date
FROM
v_numbers;
创建日期视图之后直接查询视图
SELECT
date
FROM
v_dates
WHERE
date BETWEEN '2020-01-20' AND '2021-01-24'
ORDER BY
date asc
借助help_topic表
SELECT
date_format( date_sub( '20220810', INTERVAL ( @i := @i - 1 ) DAY ), '%Y%m%d' ) AS days
FROM
mysql.help_topic
JOIN ( SELECT @i := 1 ) c
WHERE
help_topic_id <= (
TIMESTAMPDIFF( DAY, '20220810', '20220820' ))