生成日期范围的数据，包括数据中未出现的日期

Question

这是我的表结构和数据:

CREATE TABLE event (
    EventID INT(11) NOT NULL AUTO_INCREMENT,
    EventDate DATETIME DEFAULT NULL,
    Description VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (EventID)
);
INSERT INTO event (EventID, EventDate, Description) VALUES
    (1, '2011-01-01 00:00:00', 'Event 1'),
    (2, '2011-03-01 00:00:00', 'Event 2'),
    (3, '2011-06-01 00:00:00', 'Event 3'),
    (4, '2011-09-01 00:00:00', 'Event 4');

和这个查询和输出:

SELECT *
FROM EVENT
WHERE EventDate BETWEEN '2011-02-01' AND '2011-03-31'

+---------+---------------------+-------------+
| EventID | EventDate           | Description |
+---------+---------------------+-------------+
|       2 | 2011-03-01 00:00:00 | Event 2     |
+---------+---------------------+-------------+
1 row in set (0.00 sec)

我想在结果中像这样注入空日期:

+---------+---------------------+-------------+
| EventID | EventDate           | Description |
+---------+---------------------+-------------+
| NULL    | 2011-02-01 00:00:00 |  NULL       |
| NULL    | 2011-02-02 00:00:00 |  NULL       |
| NULL    | 2011-02-03 00:00:00 |  NULL       |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL    | 2011-02-28 00:00:00 |  NULL       |
|       2 | 2011-03-01 00:00:00 |  Event 2    |
| NULL    | 2011-03-02 00:00:00 |  NULL       |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL    | 2011-03-31 00:00:00 |  NULL       |
+---------+---------------------+-------------+

输出应该包含59行:2月28行，3月31行。

我想避免任何硬编码;相反，我需要一个非常灵活的解决方案来适应指定的日期。

Answer 1

辅助日历表就可以了。最简单的日历表是一列日期。

create table calendar (
    cal_date date primary key
);

您可以使用电子表格或SQL填充它。外部连接将引入数据中不存在的日期。使用GRANT和REVOKE限制权限，并使用任何必要的方法来确保您期望在那里的日期实际上在那里。我在服务器上运行每日报告，确保有'n'行，并验证最早和最晚的日期。

在某些平台上，您可以动态生成一系列日期，可以直接使用，也可以在CTE中使用。PostgreSQL有相应的函数;我不知道MySQL是否可以。不过，如果你想自己写的话，它们并不难写。

Answer 2

也许你需要一个数据透视表，看一下

数据透视表模式

名称:主列:{i:数据类型int}

填充

create foo table

模式foo

名字:foo列:value数据类型varchar

insert into foo
values('0'),
values('1'),
values('2'),
values('3'),
values('4'),
values('5'),
values('6'),
values('7'),
values('8'),
values('9');
- insert 100 values
insert into pivot
select concat(a.value, a.value)
from foo a, foo b
- insert 1000 values
insert into pivot
select concat(a.value, b.value, c.value)
from foo a, foo b, foo c

查询

SELECT 
 Event.EventId,
 case when EventDate is null then DATE_ADD(periods.periodstart, INTERVAL auxtable.i DAY)
 else EventDate  end,
 Description
FROM
(
  select id, min(EventDate ) periodstart, max(EventDate) periodend,
  DATEDIFF(max(EventDate),min(EventDate )) as days
  FROM EVEN
  WHERE EventDate BETWEEN '2011-02-01' AND '2011-03-31'
) periods
inner join     
(
  select *
  from pivot
  where i >= 0 and i < 31 //max one month change with your needs
)auxtable
on auxtable.i < periods.days
left join Event
on Event.EventDate = DATE_ADD(periods.periodstart, INTERVAL auxtable.i DAY)

Answer 3

我很确定这在纯SQL中是不可能的，所以您的选择是:

• 使用存储过程/函数
• 在应用程序代码中执行此操作，这看起来非常直接