MYSQL查询两个时间之间的日期列表

时间：2016-11-21 13:04:11

这个一般用于统计，今天在开发一个拆线时用到了。需要把日期来显示在X轴上，直接百度了下，方法有好多，

找了一个最实用的（需要建一张临时表）

CREATE TABLE num (i INT);
 
INSERT INTO num (i)
VALUES
    (0),
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9);
 
SELECT
    adddate('2012-09-01', numlist.id) AS 'date'
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
    ) AS numlist
WHERE
    adddate('2012-09-01', numlist.id) <= '2012-09-10';

方法2：

-- 定义自增变量
set @i = -1;
-- 创建指定日期间记录数天数需要 加 1
set @sql = repeat(" select 1 union all",-datediff('2012-09-01','2012-09-10')+1);
-- 将最后一个union all 去掉，这里前边加1个空格，就不用在减1了
set @sql = left(@sql,length(@sql)-length(" union all"));
-- 拼接sql
set @sql = concat("select date_add('2012-09-01',interval @i:=@i+1 day) as date from (",@sql,") as tmp");
 
prepare stmt from @sql;
execute stmt;

方法三存储过程（没测试）：

DELIMITER $$
 
DROP PROCEDURE IF EXISTS DayRangeProc$$
 
CREATE PROCEDURE DayRangeProc(IN start_date datetime , IN end_date datetime )
      BEGIN
              DECLARE temp  INT;
              DECLARE range_day  INT;
 
              SET temp = 0;
              SET range_day = (SELECT DATEDIFF(end_date,start_date));
 
              WHILE temp  <= range_day DO
                    select ADDDATE(start_date, temp);
                    SET temp = temp + 1;
              END WHILE;
      END$$
 
  DELIMITER ;
 
Call DayRangeProc('2010-9-1','2010-9-10');