SQL查询可返回表中未使用的所有日期 [英] SQL query that returns all dates not used in a table

问题描述

所以可以说我有一些看起来像这样的记录:

So lets say I have some records that look like:

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

如何构建一个查询，该查询将返回2011-01-03和2011-01-05，即未使用的日期.我将博客发布到未来，我想要一个查询来向我显示我还没有发布任何东西的日子.从当前日期到未来的2周.

How can I construct a query that will return 2011-01-03 and 2011-01-05, ie the unused dates. I postdate blogs into the future and I want a query that will show me the days I don't have anything posted yet. It would look from the current date to 2 weeks into the future.

更新:

我对建立一个永久性的日期表并不感到兴奋.经过考虑之后，解决方案似乎是制作一个小的存储过程来创建一个临时表.像这样:

I am not too excited about building a permanent table of dates. After thinking about it though it seems like the solution might be to make a small stored procedure that creates a temp table. Something like:

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 

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

推荐答案

您是对的-SQL并不容易识别丢失的数据.通常的技术是将您的序列(有间隔)连接到一个完整的序列，然后在后一个序列中选择那些元素，而数据中没有相应的伙伴.

You're right — SQL does not make it easy to identify missing data. The usual technique is to join your sequence (with gaps) against a complete sequence, and select those elements in the latter sequence without a corresponding partner in your data.

因此，@ BenHoffstein的建议维护永久日期表是一个很好的选择.

So, @BenHoffstein's suggestion to maintain a permanent date table is a good one.

简短地说，您可以使用

Short of that, you can dynamically create that date range with an integers table. Assuming the integers table has a column i with numbers at least 0 – 13, and that your table has its date column named 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;

这篇关于SQL查询可返回表中未使用的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！