生成日期范围的数据，包括数据中不存在的数据 [英] Generate data for range of dates including those that are not present in data

问题描述

这是我的表结构和数据：

Here is my table structure and data:

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)

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

I want inject empty dates in the result like so:

+---------+---------------------+-------------+
| 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日。

The output should contain 59 rows: 28 for February and 31 for March.

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

I'll want to avoid any hard coding; instead, I need a very flexible solution that adapts itself to the specified dates.

推荐答案

辅助日历表可以正常运行。最简单的日历表是​​单列日期。

An auxiliary calendar table will work well. The simplest possible calendar table is a single column of dates.

create table calendar (
    cal_date date primary key
);

您可以使用电子表格或SQL来填充它。其上的外连接将带来数据中不存在的日期。限制GRANT和REVOKE的权限，并使用任何必要的手段确保您期望在其中的日期实际上在那里。我在我的服务器上运行一个日常报告，确保有n行，并验证最早和最新的日期。

You can use a spreadsheet or SQL to populate it. An outer join on it will bring in the dates that don't exist in your data. Limit permissions with GRANT and REVOKE, and use whatever means necessary to make sure that the dates you expect to be in there are actually in there. I run a daily report on my server that makes sure there are 'n' rows, and verifies the earliest and latest dates.

在某些平台上，您可以生成一个系列的日期，并直接或在CTE中使用。 PostgreSQL有这样的功能;我不知道MySQL是不是。但是，如果你想自己滚动，他们不难写。

On some platforms, you can generate a series of dates on the fly, and either use it directly or in a CTE. PostgreSQL has functions for that; I don't know whether MySQL does. They're not hard to write, though, if you want to roll your own.

这篇关于生成日期范围的数据，包括数据中不存在的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！