如何用一系列日期填充表格? [英] How to populate a table with a range of dates?

问题描述

我需要一个 MySQL 表来保存 2011 年 1 月 1 日到 2011 年 12 月 31 日之间的所有日期.我创建了一个包含一列名称_date"的表，输入 DATE.

I need a MySQL table to hold ALL DATES between 2011-01-01 and 2011-12-31. I have created a table with one column names "_date", type DATE.

我可以通过什么查询在表格中填充所有所需的日期(而不必手动输入)?

With what query can I populate the table with all the desired dates (instead of having to enter them by hand)?

推荐答案

试试这个:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO tablename (_date) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');

这是使用它的 SQL Fiddle:http://sqlfiddle.com/#!2/65d13/1

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/65d13/1

按照 安德鲁·福克斯.

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

    WHILE dateStart <= dateEnd DO

        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);

        IF adate IS NULL THEN BEGIN

            INSERT INTO MyDates (mydate) VALUES (dateStart);

        END; END IF;

        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

    END WHILE;

END;//

这是使用它的 SQL Fiddle:http://sqlfiddle.com/#!2/66f86/1

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/66f86/1

这篇关于如何用一系列日期填充表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！