Generate data for range of dates including those t

问题:

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');

And this query and output:

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

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.

回答1:

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
);

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.

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.

回答2:

maybe you need an pivot table, Take a look at that

pivot table schema

name: pivot columns: {i : datatype int}

Populate

create foo table

schema foo

name: foo column: value datatype 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

your query

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)

回答3:

I'm fairly sure this is impossible in pure SQL, so your choices are:

• use a stored procedure/function
• do this in the app code, which seems pretty straight forward