mysql獲取一個(gè)時(shí)間段中所有日期或者月份

1：mysql獲取時(shí)間段所有月份

select DATE_FORMAT(date_add(’2020-01-20 00:00:00’, interval row MONTH),’%Y-%m’) 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 @row:=-1) r ) se where DATE_FORMAT(date_add(’2020-01-20 00:00:00’, interval row MONTH),’%Y-%m’) <= DATE_FORMAT(’2020-04-02 00:00:00’,’%Y-%m’)

2：mysql獲取時(shí)間段所有日期

select date_add(’2020-01-20 00:00:00’, 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 @row:=-1) r ) se where date_add(’2020-01-20 00:00:00’, interval row DAY) <= ’2020-03-02 00:00:00’

備注：

這段代碼表示數(shù)據(jù)條數(shù)限制，寫兩次查詢的日期最多顯示100條，寫三次查詢?nèi)掌谧疃囡@示1000次，以此類推，根據(jù)你自己的需求決定

下面是設(shè)置最多顯示條數(shù)10000寫法

希望能幫助到你，萌新在線求帶！！！

下面是其他網(wǎng)友的補(bǔ)充大家可以參考一下

1、不使用存儲(chǔ)過程，不使用臨時(shí)表，不使用循環(huán)在Mysql中獲取一個(gè)時(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) awhere a.Date between ’2017-11-10’ and ’2017-11-15’

輸出如下

Date----------2017-11-152017-11-142017-11-132017-11-122017-11-112017-11-10

2、mysql獲取兩個(gè)日期內(nèi)的所有日期列表

select @num:=@num+1,date_format(adddate(’2015-09-01’, INTERVAL @num DAY),’%Y-%m-%d’) as datefrom btc_user,(select @num:=0) t where adddate(’2015-09-01’, INTERVAL @num DAY) <= date_format(curdate(),’%Y-%m-%d’)order by date;

此方法優(yōu)點(diǎn)就是不需要?jiǎng)?chuàng)建存儲(chǔ)過程或者是日歷表，缺點(diǎn)就是你必須要有一個(gè)表，它的數(shù)據(jù)條數(shù)大到足夠支撐你要查詢的天數(shù)

3、mysql獲取給定時(shí)間段內(nèi)的所有日期列表(存儲(chǔ)過程)

DELIMITER $$DROP PROCEDURE IF EXISTS create_calendar $$CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)BEGIN-- 生成一個(gè)日歷表SET @createSql = ‘CREATE TABLE IF NOT EXISTS calendar_custom (`date` date NOT NULL,UNIQUE KEY `unique_date` (`date`) USING BTREE)ENGINE=InnoDB DEFAULT CHARSET=utf8‘;prepare stmt from @createSql;execute stmt;WHILE s_date <= e_date DOINSERT IGNORE INTO calendar_custom VALUES (DATE(s_date)) ;SET s_date = s_date + INTERVAL 1 DAY ;END WHILE ;END$$DELIMITER ;-- 生成數(shù)據(jù)到calendar_custom表2009-01-01~2029-01-01之間的所有日期數(shù)據(jù)CALL create_calendar (‘2009-01-01‘, ‘2029-01-01‘);DELIMITER $$DROP PROCEDURE IF EXISTS create_calendar $$CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)BEGIN-- 生成一個(gè)日歷表SET @createSql = ‘truncate TABLE calendar_custom‘;prepare stmt from @createSql;execute stmt;WHILE s_date <= e_date DOINSERT IGNORE INTO calendar_custom VALUES (DATE(s_date)) ;SET s_date = s_date + INTERVAL 1 DAY ;END WHILE ;END$$DELIMITER ;-- 生成數(shù)據(jù)到calendar_custom表2009-01-01~2029-01-01之間的所有日期數(shù)據(jù)CALL create_calendar (‘2009-01-02‘, ‘2009-01-07‘);

到此這篇關(guān)于mysql獲取指定時(shí)間段中所有日期或月份的語句(不設(shè)存儲(chǔ)過程，不加表)的文章就介紹到這了,更多相關(guān)mysql獲取指定時(shí)間段中的日期與月份內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！