mysql获取指定时间段中所有日期或月份的语句(不设存储过程，不加表)

mysql获取一个时间段中所有日期或者月份

1：mysql获取时间段所有月份

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')

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'

这段代码表示数据条数限制，写两次查询的日期最多显示100条，写三次查询日期最多显示1000次，以此类推，根据你自己的需求决定

下面是设置最多显示条数10000写法

希望能帮助到你，萌新在线求带！！！

下面是其他网友的补充大家可以参考一下

1、不使用存储过程，不使用临时表，不使用循环在Mysql中获取一个时间段的全部日期

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-15
2017-11-14
2017-11-13
2017-11-12
2017-11-11
2017-11-10

2、mysql获取两个日期内的所有日期列表

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;

3、mysql获取给定时间段内的所有日期列表(存储过程)

DELIMITER $$DROP PROCEDURE IF EXISTS create_calendar $$CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)BEGIN-- 生成一个日历表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 ;-- 生成数据到calendar_custom表2009-01-01~2029-01-01之间的所有日期数据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-- 生成一个日历表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 ;-- 生成数据到calendar_custom表2009-01-01~2029-01-01之间的所有日期数据CALL create_calendar (‘2009-01-02‘, ‘2009-01-07‘);

更多相关文章

随机推荐