mysql獲取時(shí)間段所有日期
-
方式1
select date_add('2020-01-01', interval row DAY) date from
(
SELECT @row := @row + 1 as row FROM
(select 0 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) t,
(select 0 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) t2,
(select 0 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) t3,
(SELECT @row:=-1) r
) se
where date_add('2020-01-01', interval row DAY) <= '2020-12-31'
這段代碼表示數(shù)據(jù)條數(shù)限制蚓哩，寫兩次查詢的日期最多顯示100條帖汞，寫三次查詢?nèi)掌谧疃囡@示1000次纬朝，以此類推个绍，根據(jù)你自己的需求決定
# 需要增加行數(shù)，就增加一條此查詢
(select 0 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)
-
方式2
當(dāng)前時(shí)間 CURDATE() - (a.a + (10 * b.a) + (100 * c.a))計(jì)算出來的天數(shù)
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.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
) a
where a.Date between '2017-11-10' and '2017-11-15'
a写穴、b顷级、c分別代表個(gè)、十确垫、百數(shù)，最高能往前推 999 天帽芽，如果要獲得更多天數(shù)删掀，可增加一個(gè)千位
# 增加一個(gè) cross join d，在select 中增加一個(gè)千位數(shù) (1000 * d.a)
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 '2000-01-01' and '2000-12-31'
