SQL开发知识：Mysql查询时间区间日期列表实例代码

select a.date
from (
select curdate() – INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date
from (select 0 as a 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 a
cross join (select 0 as a 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 b
cross join (select 0 as a 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 c
cross join (select 0 as a 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 d
) a
where a.date between ‘2020-01-20’ and ‘2021-12-24’ ORDER BY a.date asc

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

附：在对mysql的时间进行区间查询的时候出现的问题