从数据库中获取所有缺失的日期记录

对于PostgreSQL，使用generate_series()函数动态生成"日期列表"是很容易的:

with all_dates as (
   select cast((current_date - interval '4' month) as date) + i as the_date
   from generate_series(0, extract(day from current_date - (current_date - interval '4' month))::int) as i
) 
select ad.the_date, 
       y.id,
       y.numOfUsers
from all_dates t
  left join your_table y ON y.dateCreated = t.the_date; 

没有一个语句可以实现所有数据库的工作…对于Oracle，您可以这样做(MyTable是您想要检查缺失日期的DB表):

SELECT * FROM
(
SELECT A.MinD + MyList.L TheDate FROM
(SELECT  MIN (dateCreated ) MinD FROM MyTable) A,
(SELECT LEVEL - 1 L FROM DUAL CONNECT BY LEVEL <= (SELECT  Max (dateCreated ) - MIN (dateCreated ) + 1 FROM MyTable)) MyList
) D WHERE D.TheDate NOT IN ( SELECT dateCreated FROM MyTable T)

假设使用MySQL，您可以使用一个变量在查询结果的每一行中携带状态:

SELECT @last := 'date you want to start with';
SELECT id, dateCreated, DATE_DIFF(dateCreated, @last) AS diff, @last := dateCreated
FROM yourtable
ORDER BY dateCreated ASC
HAVING diff > 1

请注意，这不会返回实际丢失的天数，但它将返回丢失日期之后的行，以及丢失的天数。

有一种不需要日期表、开始日期和结束日期或任何其他形式的迭代的方法。

select DATEADD(day,1,left.dateCreated) as MissingDates
from dbo.MyTable as left
left outer join dbo.MyTable as right on DATEADD(day,1,left.dateCreated) = right.entry_time
where right.dateCreated is null

这将返回一列的开始日期的跨度缺失的日期。然后还可以创建另一个列，该列通过在第二个比较表中减去1而不是添加一天来返回缺失日期范围内的最后一个日期。

很容易，最有效的方法(在我看来)是，如您所说，从所有日期的表格开始。您必须自己创建它，假设您已经完成了，这里有几个选项供您选择…

SELECT
  *
FROM
  calendar    -- Your manually created table of dates
LEFT JOIN
  yourTable
    ON yourTable.DateField = calendar.DateField
WHERE
  yourTable.DateField IS NULL
  AND calendar.DateField >= @reportFirstDate
  AND calendar.DateField <= @reportLastdate

还是……

SELECT
  *
FROM
  calendar    -- Your manually created table of dates
WHERE
  NOT EXISTS (SELECT * FROM yourTable WHERE yourTable.DateField = calendar.DateField)
  AND calendar.DateField >= @reportFirstDate
  AND calendar.DateField <= @reportLastdate

编辑

虽然维护这个日期列表会让人感觉"不整洁"，但对于这种类型的查询来说，它有巨大的性能优势。

使用日期表，您将查看两个索引，并检查其中一个存在而另一个不存在的内容。

没有日期表，你有一个更复杂的方法…
1. 取表
中的每条记录2. Self将它连接到表
中的Next记录3.如果它们是连续的日期，则丢弃它们(保持记录之间的间隔)
4. 对于每一对，循环遍历，填充缺失的日期
5. 处理报告周期开始时缺少的日期(对中没有Date1)
6. 处理报告期末缺少的日期(对中没有Date2)

用日期创建一个临时表可能更快，我们直接做，然后再次删除表。如果是这样的话，为什么不维护日期表呢?

1. 只要有一个100年的表，忘了它吧
2. 有一个非常快速的代码，以保持最新的日期在所有的代码

如果你不相信，我建议你尝试不同的选项，看看索引日期表与其他选项相比有多快。

(更不用说更短，可读性和可维护性)

MySQL:

假设我们有表yourTable和日期字段d:

set @day = 0;
select v.y as `month`, v.m as `month`, v.d as `day`
from 
(
   select y_m.y, Y_m.m, dd.d
   from
   (
     select distinct year(d) y, month(d) m, DAY(LAST_DAY(yourTable.d)) max_days from yourTable
   ) y_m,
   (
   select  @day := @day+1 as `d`
   from
     information_schema.tables
   limit 32
   ) dd
   where y_m.max_days >= dd.d
) v
left join
   yourTable on (year(yourTable.d) = v.y and month(yourTable.d) = v.m and day(yourTable.d) = v.d)
where yourTable.d is null
;