oracle选择时间,Oracle：选择缺少的日期

小编典典

这样的事情(假设您的表被命名your_table，而date列被命名the_date)：

with date_range as (

select min(the_date) as oldest,

max(the_date) as recent,

max(the_date) - min(the_date) as total_days

from your_table

),

all_dates as (

select oldest + level - 1 as a_date

from date_range

connect by level <= (select total_days from date_range)

)

select ad.a_date

from all_dates ad

left join your_table yt on ad.a_date = yt.the_date

where yt.the_date is null

order by ad.a_date;

编辑：

该WITH子句称为“公用表表达式”，等效于派生表(“内联视图”)。

类似于

select *

from (

.....

) all_dates

join your_table ...

第二个CTE使用Oracleconnect by实现中未记录的功能简单地“即时”创建日期列表。

与使用派生表相比，重新使用选择(就像我计算第一个和最后一个日期所做的那样)要容易一些(恕我直言，更易读)。

编辑2：

这也可以通过递归CTE来完成：

with date_range as (

select min(the_date) as oldest,

max(the_date) as recent,

max(the_date) - min(the_date) as total_days

from your_table

),

all_dates (a_date, lvl) as (

select oldest as a_date, 1 as lvl

from date_range

union all

select (select oldest from date_range) + lvl, lvl + 1

from all_dates

where lvl < (select total_days from date_range)

)

select ad.a_date, lvl

from all_dates ad

left join your_table yt on ad.a_date = yt.the_date

where yt.the_date is null

order by ad.a_date;

哪个应该在所有支持递归CTE的DBMS中都起作用(PostgreSQL和Firebird-更符合标准-确实需要recursive关键字)。

请注意select (select oldest from date_range) + lvl, lvl + 1递归部分中的hack

。这不是必须的，但是在递归CTE中，Oracle在DATE方面仍然存在一些错误。在PostgreSQL中，以下工作没有问题：

....

all_dates (a_date, lvl) as (

select oldest as a_date, 0 as lvl

from date_range

union all

select a_date + 1, lvl + 1

from all_dates

where lvl < (select total_days from date_range)

)

....

2021-03-23