Генерировать данные для диапазона дат, в том числе тех, которые отсутствуют в данных

Вот моя структура и данные таблицы:

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 строк: 28 за февраль и 31 марта.

Я хочу избежать жесткого кодирования; вместо этого мне нужно очень гибкое решение, которое адаптируется к указанным датам.

Вспомогательная таблица календаря будет работать хорошо. Самая простая таблица календаря – это один столбец дат.

 create table calendar ( cal_date date primary key ); 

Вы можете использовать электронную таблицу или SQL для ее заполнения. Внешнее соединение на нем приведет к датам, которые не существуют в ваших данных. Ограничьте разрешения с помощью GRANT и REVOKE и используйте любые средства, необходимые для того, чтобы убедиться, что даты, которые вы ожидаете, находятся там. Я запускаю ежедневный отчет на своем сервере, который обеспечивает наличие «n» строк и проверяет самые ранние и последние даты.

На некоторых платформах вы можете генерировать серию дат «на лету» и использовать их напрямую или в CTE. PostgreSQL имеет для этого функции; Я не знаю, работает ли MySQL. Их нетрудно написать, хотя, если вы хотите бросить свои собственные.

может быть, вам нужен сводный стол, взгляните на это

схема сводной таблицы

name: pivot columns: {i: datatype int}

населять

создать таблицу foo

схема foo

name: foo column: значение 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 

Ваш запрос

 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) 

Я уверен, что это невозможно в чистом SQL, поэтому ваши варианты:

  • использовать хранимую процедуру / функцию
  • сделайте это в коде приложения, который выглядит довольно прямолинейно