遍历一段时间内的所有日期
SELECT DISTINCT
date_format( DATE_ADD( '2021-03-01', INTERVAL id DAY ), '%Y-%m-%d' ) AS `date`
FROM
student
WHERE
DATE_ADD( '2021-03-01', INTERVAL id DAY ) <= '2021-03-10'
ORDER BY
date
注:查询表为数据量足够大的表,行数必须大于时间跨度(天)。 若小于时间跨度只能查询出表中行数。
创建一个student表
CREATE TABLE `student` (
`id` INT ( 255 ) DEFAULT NULL,
`math` INT ( 255 ) DEFAULT NULL,
`english` INT ( 255 ) DEFAULT NULL,
`yuwen` INT ( 255 ) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;
向表中插入30条id
DELIMITER;;
CREATE PROCEDURE student_creater () BEGIN#Routine body goes here...
DECLARE
a INT DEFAULT 1;
WHILE
a < 30 DO
INSERT INTO student ( `id` )
VALUES
( a );
SET a = a + 1;
END WHILE;
END;;
CALL student_creater ();