创建一个带有日期的临时表 [英] Create a temporary table with dates

问题描述

如何创建一个临时表并在其中填入ceratin日期.我知道开始日期和限制"

How can I create a temporary table and fill it with ceratin dates. I know the startdate and the "limit"

• 开始日期 = 2014-11-11
• 限制 = 3

• startdate = 2014-11-11
• limit = 3

表应该看起来像

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

我喜欢使用此创建表将其与另一个表连接

I like to use this create table to join it with another

推荐答案

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

You can generate the dynamic dates and then insert them int to the table as below. I have used a table instead of temporary table you can change it to temporary table.

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

这是 演示

Here is a demo

这篇关于创建一个带有日期的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！