这个一般用于统计,今天在开发一个拆线时用到了。需要把日期来显示在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');