使用SQL查找丢失的日期 [英] Find missing dates using SQL
问题描述
像这样的示例日期,我在表中有一些两年的日期
I have some dates in table over of a two years like this as a example date
01-jan-2012
02-jan-2012
04-jan-2012
05-jan-2012
06-jan-2012
07-jan-2012
09-jan-2012
11-jan-2012
.
.
.
01-DEC-2012
我认为您已经注意到03-jan-2012
和08-jan-2012
缺少日期,并且所有日期都具有相同的条件.我的问题是,oracle中有什么方法可以找到丢失的日期.请帮助! >
I think you have noticed that there a missing date of 03-jan-2012
and 08-jan-2012
and the same criteria with all dates.My Question is that is there any way in oracle to find the missing dates.Plz Help !
推荐答案
这将使您一年中所有失踪的日子( SQL小提琴).
This will get you all missing days for one year (SQL Fiddle).
all_dates
生成2012年所有日期的列表(根据需要进行调整),而LEFT JOIN
检查IS NULL
则消除了源表中存在的那些日期.
all_dates
generates a list of all dates of 2012 (adjust as required), and the LEFT JOIN
checking for IS NULL
eliminates those dates that exist in your source-table.
WITH all_dates AS (
SELECT TO_DATE('01-jan-2012') + ROWNUM - 1 AS d
FROM dual
CONNECT BY ROWNUM <= ADD_MONTHS(TO_DATE('01-jan-2012'), 12 ) - TO_DATE('01-jan-2012')
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.d = all_dates.d )
WHERE t.d IS NULL
ORDER BY all_dates.d
;
确保使用绑定变量,而不是对日期进行3次硬编码.
Make sure to use a bind variable instead of hard-coding the date three times.
这篇关于使用SQL查找丢失的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!