创建一个带有日期的临时表

Question

如何创建临时表并将其填充到ceratin日期。我知道开始日期和“极限”

• startdate = 2014-11-11
• 极限=3

表应该看起来像

2014-11-11
2014-11-12
2014-11-13

我喜欢使用这个create将它与另一个

Answer 1

您可以生成动态日期，然后将它们插入表中，如下所示。我使用了一个表而不是临时表，您可以将它更改为临时表。

CREATE TEMPORARY TABLE IF NOT EXISTS dates_test
(dates datetime);


insert into dates_test (dates)
select 
t1.date
from
(
  select
  a.Date as 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
  ) a
  where a.Date BETWEEN '2014-11-11' 
  and
  DATE_ADD('2014-11-11' ,INTERVAL 3 DAY)
)t1

这是一个演示