返回表中未使用的所有日期的 SQL 查询

Question

假设我有一些如下所示的记录：

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

如何构造一个返回 2011-01-03 和 2011-01-05（即未使用的日期）的查询。我将博客发布到未来，我想要一个查询来显示我还没有发布任何内容的日子。它将看起来从当前日期到未来两周。

更新：

我对建立一个永久的日期表并不太感兴趣。经过思考后，解决方案似乎是创建一个创建临时表的小型存储过程。比如：

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 

我想不可能选择缺少数据。

Answer 1

您是对的 — 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;

Answer 2

一种解决方案是创建一个单独的表，其中有一列来保存从现在到永恒（或每当您希望停止写博客时）的所有日期。例如：

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)

Answer 3

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

Answer 4

您可能不会喜欢这样：

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

OR

创建一个包含 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;