SQL-запрос, который возвращает все даты, не используемые в таблице

Итак, скажем, у меня есть несколько записей, которые выглядят так:

2011-01-01 Cat
2011-01-02 Dog
2011-01-04 Horse
2011-01-06 Lion

Как я могу построить запрос, который вернет 2011-01-03 и 2011-01-05, то есть неиспользованные даты. Я отношу блоги к будущему, и мне нужен запрос, который покажет мне дни, когда я еще ничего не публиковал. Это будет выглядеть от текущей даты до 2 недель в будущем.

Обновление:

Я не слишком взволнован созданием постоянной таблицы дат. Подумав об этом, кажется, что решением может быть создание небольшой хранимой процедуры, которая создает временную таблицу. Что-то вроде:

CREATE PROCEDURE MISSING_DATES()
BEGIN
    CREATE TABLE TEMPORARY DATES (FUTURE DATETIME NULL)
    INSERT INTO DATES (FUTURE) VALUES (CURDATE())
    INSERT INTO DATES (FUTURE) VALUES (ADDDATE(CURDATE(), INTERVAL 1 DAY))
    ...
    INSERT INTO DATES (FUTURE) VALUES (ADDDATE(CURDATE(), INTERVAL 14 DAY))

    SELECT FUTURE FROM DATES WHERE FUTURE NOT IN (SELECT POSTDATE FROM POSTS)

    DROP TABLE TEMPORARY DATES
END

Я думаю, просто невозможно выбрать отсутствие данных.


mysql sql stored-procedures gaps-and-islands
person Jack B Nimble    schedule 30.12.2010    source источник
comment
Отличный вопрос. Жду ответов, потому что я тоже не знаю, как это сделать.   -  person Corey    schedule 30.12.2010
comment
Вы будете удивлены, насколько удобной может быть постоянная таблица дат. В дополнение к решению вашей непосредственной проблемы, если вы добавите суррогатный ключ, это сведет такие проблемы, как вычисление количества дней между датами, на какой день недели приходится день и т. д., до простой арифметики. ИМО, решение для временной таблицы неэлегантно и не избавляет вас от работы.   -  person Ben Hoffstein    schedule 31.12.2010

Ответы (4)


arrow_upward
4
arrow_downward

Вы правы — SQL не позволяет легко идентифицировать отсутствующие данные. Обычный метод состоит в том, чтобы соединить вашу последовательность (с пробелами) с полной последовательностью и выбрать эти элементы в последней последовательности без соответствующего партнера в ваших данных.

Итак, предложение @BenHoffstein поддерживать постоянную таблицу дат является хорошим.

Кроме того, вы можете динамически создать этот диапазон дат с помощью таблица целых чисел. Предположим, что в таблице integers есть столбец i с номерами не менее 0–13, и что в вашей таблице есть столбец дат с именем datestamp:

   SELECT candidate_date AS missing
     FROM (SELECT CURRENT_DATE + INTERVAL i DAY AS candidate_date
             FROM integers
            WHERE i < 14) AS next_two_weeks
LEFT JOIN my_table ON candidate_date = datestamp
    WHERE datestamp is NULL;
person pilcrow    schedule 10.09.2012
comment
создание таблицы временных целых чисел со значениями 1-14 и запуск этого скрипта дает именно то, что я изначально хотел, список, выходящий на две недели, в котором перечислены только даты без комикса. - person Jack B Nimble; 10.09.2012
comment
@JackBNimble, это сработает. Я бы порекомендовал держать под рукой таблицу или представление с числом от 0 до 999, чтобы вы могли с легкостью заглянуть на три недели вперед или на шесть месяцев назад. (Я поставил его в util.integers.) - person pilcrow; 10.09.2012

arrow_upward
4
arrow_downward

Одним из решений было бы создать отдельную таблицу с одним столбцом для хранения всех дат с настоящего момента до вечности (или всякий раз, когда вы планируете прекратить вести блог). Например:

CREATE TABLE Dates (dt DATE);
INSERT INTO Dates VALUES ('2011-01-01');
INSERT INTO Dates VALUES ('2011-01-02');
...etc...
INSERT INTO Dates VALUES ('2099-12-31');

После того, как эта справочная таблица настроена, вы можете просто выполнить внешнее соединение, чтобы определить неиспользуемые даты следующим образом:

SELECT d.dt 
FROM Dates d LEFT JOIN Blogs b ON d.dt = b.dt 
WHERE b.dt IS NULL

Если вы хотите ограничить поиск двумя неделями в будущем, вы можете добавить это в предложение WHERE:

AND d.dt BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 14 DAY)
person Ben Hoffstein    schedule 30.12.2010
comment
+1 Преимущество таблицы календаря (в отличие от, скажем, динамического создания ряда дат, как в моем ответе) заключается в разборчивости - легко увидеть, что пытается сделать запрос. - person pilcrow; 10.09.2012

arrow_upward
0
arrow_downward

Способ извлечения строк из базы данных mysql — через SELECT. Таким образом, вы не можете выбрать несуществующие строки.

Что бы я сделал, так это заполнил таблицу своего блога всеми возможными датами (на год, затем повторил процесс)

 create table blog (
    thedate date not null,
    thetext text null,
    primary key (thedate));

выполнение цикла для создания всех записей дат на 2011 год (используя программу, например, $mydate — это дата, которую вы хотите вставить)

  insert IGNORE into blog (thedate,thetext) values ($mydate, null);

(ключевое слово IGNORE, чтобы не создавать ошибку (дата является первичным ключом), если дата уже существует).
Затем вы обычно вставляете значения

  insert into blog (thedate,thetext) values ($mydate, "newtext") 
  on duplicate key update thetext="newtext";

Наконец, чтобы выбрать пустые записи, вам просто нужно

  select thedate from blog where thetext is null;
person Breaking not so bad    schedule 30.12.2010

arrow_upward
0
arrow_downward

Вряд ли вам понравится это:

select '2011-01-03', count(*) from TABLE where postdate='2011-01-03' 
  having count(*)=0 union
select '2011-01-04', count(*) from TABLE where postdate='2011-01-04' 
  having count(*)=0 union
select '2011-01-05', count(*) from TABLE where postdate='2011-01-05' 
  having count(*)=0 union
... repeat for 2 weeks

ИЛИ

создайте таблицу со всеми днями в 2011 году, затем выполните левое соединение, например

select a.days_2011
from all_days_2011
left join TABLE on a.days_2011=TABLE.postdate
where a.days_2011 between date(now()) and date(date_add(now(), interval 2 week))
and TABLE.postdate is null;
person ajreal    schedule 30.12.2010