MySQL：查找日期范围内缺失的日期

Question

我需要一些有关 mysql 查询的帮助。我有一个数据库表，其中包含从 2011 年 1 月 1 日到 2011 年 4 月 30 日的数据。每个日期都应该有一条记录。我需要查明表中是否缺少任何日期。

例如，假设 2011 年 2 月 2 日没有数据。我如何找到该日期？

我将日期存储在名为 reportdatetime 的列中。日期存储格式为：2011-05-10 0:00:00，即 2011 年 5 月 5 日凌晨 12:00:00。

有什么建议吗？

Answer 1

这是第二个答案，我会单独发布。

SELECT DATE(r1.reportdate) + INTERVAL 1 DAY AS missing_date
FROM Reports r1
LEFT OUTER JOIN Reports r2 ON DATE(r1.reportdate) = DATE(r2.reportdate) - INTERVAL 1 DAY
WHERE r1.reportdate BETWEEN '2011-01-01' AND '2011-04-30' AND r2.reportdate IS NULL;

这是一个自连接，它报告一个日期，使得不存在后面的日期的行。

这将找到间隙中的第一天，但​​如果缺少多天的运行，则不会报告间隙中的所有日期。

Answer 2

1. CREATE TABLE Days (day DATE PRIMARY KEY);

2. 填写 Days< /code> 包含您要寻找的所有日子。

   mysql>插入天数值 ('2011-01-01');
   mysql>设置@偏移量：= 1；
   mysql> INSERT INTO Days SELECT day + INTERVAL @offset DAY FROM Days；设置@offset := @offset * 2;
   

   然后向上箭头并根据需要重复 INSERT 多次。它每次都会使行数加倍，因此您可以在七个 INSERT 中获得四个月的行数。

3. 执行排除联接以查找报告表中没有匹配项的日期：

   从 d 天中选择 d.day 
   左外连接报告 r ON d.day = DATE(r.reportdatetime) 
   “2011-01-01”和“2011-04-30”之间的 d.day 
       AND r.reportdatetime 为 NULL;`
   

Answer 3

可以使用更复杂的单个查询来完成，但我将显示一个带有临时表的伪代码，仅用于说明：

获取我们有记录的所有日期：

CREATE TEMP TABLE AllUsedDates

SELECT DISTINCT reportdatetime
INTO AllUsedDates;

现在添加 5 月 1 日，以便我们跟踪 04 -30

INSERT INTO AllUsedData ('2011-05-01')

如果没有“第二天”，我们就发现了一个缺口：

SELECT A.NEXT_DAY
FROM
    (SELECT reportdatetime AS TODAY, DATEADD(reportdatetime, 1) AS NEXT_DAY FROM AllUsed Dates) AS A
WHERE
    (A.NEXT_DATE NOT IN (SELECT reportdatetime FROM AllUsedDates)
    AND
    A.TODAY <> '2011-05-01') --exclude the last day

Answer 4

如果您的意思是 reportdatetime 具有“2011 年 2 月 2 日”条目，但与该日期关联的其他字段不存在，如下表快照所示，

reportdate  col1    col2
5/10/2011   abc xyz
2/2/2011        
1/1/2011    bnv oda

那么此查询工作正常

select reportdate from dtdiff where reportdate not in (select df1.reportdate from dtdiff df1, dtdiff df2 where df1.col1 = df2.col1)

Answer 5

。

SELECT DATE(t1.datefield) + INTERVAL 1 DAY AS missing_date FROM table t1 LEFT OUTER JOIN table t2 ON DATE(t1.datefield) = DATE(t2.datefield) - INTERVAL 1 DAY WHERE DATE(t1.datefield) BETWEEN '2020-01-01' AND '2020-01-31' AND DATE(t2.datefield) IS NULL;

如果您想获取日期时间字段中缺少的日期，请尝试此操作

SELECT CAST(t1.datetime_field  as DATE) + INTERVAL 1 DAY AS missing_date FROM table t1 LEFT OUTER JOIN table t2 ON CAST(t1.datetime_field  as DATE) = CAST(t2.datetime_field  as DATE) - INTERVAL 1 DAY WHERE CAST(t1.datetime_field  as DATE) BETWEEN '2020-01-01' AND '2020-07-31' AND CAST(t2.datetime_field  as DATE) IS NULL;

Answer 6

上面的解决方案似乎有效，但至少在我的数据库中它们看起来非常慢（可能需要几个小时，我只等了 30 分钟）。

该子句在同一数据库中花费的时间不到一秒钟（当然，您需要手动重复数十次，并且可能更改函数名称以查找实际日期）。 pvm = 我的日期时间，天气 = 我的桌子。

mysql>选择年份(pvm) as _year,count(distinct(date(pvm))) as _days from WEATHER whereyear(pvm)>=2000 and Month(pvm)=1 group by _year order by _year asc;

--亚子