MySQL:将日期范围扩展到新行 [英] MySQL: Expand date range into new rows

问题描述

我在MySQL中有一个表，其中包含每个键的最小和最大日期值:

I have a table in MySQL that contains min and max date values for each key:

key |   minDate   |   maxDate
 1     2011-01-01   2011-01-10 
 2     2011-02-13   2011-02-15
 3     2011-10-19   2011-12-10

如何创建一个新表，其中每个键的minDate和maxDate之间的每个日期都包含一行:

How can I create a new table that contains one row for each date between minDate and maxDate for each of the keys:

key |     Date   
 1     2011-01-01
 1     2011-01-02
 ...     ...
 1     2011-01-10
 2     2011-02-13
 2     2011-02-14
 2     2011-02-15
 3     2011-10-19
 ...     ...

推荐答案

使用整数表，您可以执行以下操作:

Using an integers table, you can do this:

    SELECT "key", minDate + INTERVAL i DAY
      FROM mytable
INNER JOIN integers
           ON i <= DATEDIFF(maxDate, minDate)

当然，假设"integers"表的列名为"i".

That assumes the "integers" table has its column named "i", of course.

从那里可以用INSERT INTO ... SELECT填充新表.

From there you can populate your new table with INSERT INTO ... SELECT.

这篇关于MySQL:将日期范围扩展到新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！