从日期范围找到缺少的日期 [英] find missing dates from date range

问题描述

我有关于获取数据库表中不存在的日期的查询。

I have query regarding get the dates which are not exists in database table.

我在数据库中有以下日期。

I have below dates in database.

2013-08-02
2013-08-02
2013-08-02
2013-08-03
2013-08-05
2013-08-08
2013-08-08
2013-08-09
2013-08-10
2013-08-13
2013-08-13
2013-08-13

我想要的结果是预期如下，

and i want the result which is expected as below,

2013-08-01
2013-08-04
2013-08-06
2013-08-07
2013-08-11
2013-08-12

你可以看到结果有六个日期不存在于数据库中，

as you can see result has six dates which are not present into database,

我已经尝试过下面的查询

i have tried below query

SELECT
    DISTINCT DATE(w1.start_date) + INTERVAL 1 DAY AS missing_date
FROM
    working w1
LEFT JOIN
    (SELECT DISTINCT start_date FROM working ) w2 ON DATE(w1.start_date) = DATE(w2.start_date) - INTERVAL 1 DAY
WHERE
    w1.start_date BETWEEN '2013-08-01' AND '2013-08-13'
AND
    w2.start_date IS NULL;

但以上返回以下结果。

2013-08-04
2013-08-14
2013-08-11
2013-08-06

你可以看到它给我四个日期从那个14是不需要的，但它仍然不包含3个日期它因为左连接。

as you can see its giving me back four dates from that 14 is not needed but its still not contain 3 dates its because of left join.

现在请看看我的查询，让我知道什么是最好的方式我可以这样做吗？

Now please look into my query and let me know what are the best way i can do this?

感谢您的查找和给予时间。

Thanks for looking and giving time.

推荐答案

我想你可以总是生成日期序列，只需使用 NOT IN 来消除实际存在的日期。这将在1024天的范围内最大化，但容易缩小或延长，日期列称为mydate，在表table1中;

I guess you could always generate the date sequence and just use a NOT IN to eliminate the dates that actually exist. This will max out at a 1024 day range, but is easy to shrink or extend, the date column is called "mydate" and is in the table "table1";

SELECT * FROM (
  SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) day 
  FROM 
   (SELECT 0 t4    UNION ALL SELECT 1   UNION ALL SELECT 2   UNION ALL SELECT 3  ) t4,
   (SELECT 0 t16   UNION ALL SELECT 4   UNION ALL SELECT 8   UNION ALL SELECT 12 ) t16,   
   (SELECT 0 t64   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48 ) t64,      
   (SELECT 0 t256  UNION ALL SELECT 64  UNION ALL SELECT 128 UNION ALL SELECT 192) t256,     
   (SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024     
  ) b 
WHERE day NOT IN (SELECT mydate FROM Table1) AND day<'2013-08-13';

从我将添加一个SQLfiddle如果没有关闭dept。

From the "I would add an SQLfiddle if it wasn't down" dept.

感谢您的帮助，这里是我结束的查询及其工作

SELECT * FROM
(
    SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) missingDates 
        FROM 
    (SELECT 0 t4    UNION ALL SELECT 1   UNION ALL SELECT 2   UNION ALL SELECT 3  ) t4,
    (SELECT 0 t16   UNION ALL SELECT 4   UNION ALL SELECT 8   UNION ALL SELECT 12 ) t16,   
    (SELECT 0 t64   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48 ) t64,      
    (SELECT 0 t256  UNION ALL SELECT 64  UNION ALL SELECT 128 UNION ALL SELECT 192) t256,     
    (SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024     
) b 
WHERE
    missingDates NOT IN (SELECT DATE_FORMAT(start_date,'%Y-%m-%d')
            FROM
                working GROUP BY start_date)
    AND
    missingDates < '2013-08-13';

这篇关于从日期范围找到缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！