如何在oracle中具有日期的两行之间查找特定日期？

Kuntal Shaw 发表于 Dev

昆塔·肖

我是一个初学者，正在学习Oracle查询并遇到了问题。

考虑下表：

    RecordNumber|   StartDate   |   StopDate
-------------------------------------------------
        1211    |   15-Oct-15   |   17-Oct-15
        1211    |   18-Oct-15   |   25-Oct-15
        1211    |   28-Oct-15   |   02-Nov-15
        1211    |   05-Nov-15   |   18-Nov-15
        1211    |   21-Nov-15   |   23-Nov-15
        1012    |   18-Sep-15   |   19-Nov-15
        1012    |   21-Nov-15   |    (null)

表名-记录

一条记录从其StartDate到StopDate是活动的。如果StopDate为null，则表示该记录仍处于活动状态。我不愿意在Oracle中编写查询，将最后一个StopDate视为终止日期，以查找记录处于非活动状态的日期。

输出应类似于：

RecordNumber| InactiveDate   
------------------------------------
    1211    |   26-Oct-15     
    1211    |   27-Oct-15      
    1211    |   03-Nov-15      
    1211    |   04-Nov-15      
    1211    |   19-Nov-15       
    1211    |   20-Nov-15   
    1012    |   20-Nov-15

任何帮助，将不胜感激。谢谢。

托尼·安德鲁斯

首先，您需要一种生成所有感兴趣日期的方法-例如，从15年10月26日到15年11月20日（包括首尾两天）的所有日期（因为这是示例输出中使用的范围）。该查询将执行以下操作：

select date '2015-10-25' + rownum as d
from dual
connect by level < 30;

D
---------
26-OCT-15
27-OCT-15
28-OCT-15
29-OCT-15
30-OCT-15
31-OCT-15
01-NOV-15
02-NOV-15
03-NOV-15
04-NOV-15
05-NOV-15
06-NOV-15
07-NOV-15
08-NOV-15
09-NOV-15
10-NOV-15
11-NOV-15
12-NOV-15
13-NOV-15
14-NOV-15
15-NOV-15
16-NOV-15
17-NOV-15
18-NOV-15
19-NOV-15
20-NOV-15

然后，您可以将其交叉连接到记录数据，以获得感兴趣的所有recordnumber日期组合的笛卡尔乘积：

with dates as
( select date '2015-10-25' + rownum as d
  from dual
  connect by level < 27
)

select distinct recordnumber, d
from   records r
       cross join dates   

RECORDNUMBER D
------------ ---------
        1012 26-OCT-15
        1012 27-OCT-15
        1012 28-OCT-15
        1012 29-OCT-15
        1012 30-OCT-15
        1012 31-OCT-15
        1012 01-NOV-15
        1012 02-NOV-15
        1012 03-NOV-15
        1012 04-NOV-15
        1012 05-NOV-15
        1012 06-NOV-15
        1012 07-NOV-15
        1012 08-NOV-15
        1012 09-NOV-15
        1012 10-NOV-15
        1012 11-NOV-15
        1012 12-NOV-15
        1012 13-NOV-15
        1012 14-NOV-15
        1012 15-NOV-15
        1012 16-NOV-15
        1012 17-NOV-15
        1012 18-NOV-15
        1012 19-NOV-15
        1012 20-NOV-15
        1211 26-OCT-15
        1211 27-OCT-15
        1211 28-OCT-15
        1211 29-OCT-15
        1211 30-OCT-15
        1211 31-OCT-15
        1211 01-NOV-15
        1211 02-NOV-15
        1211 03-NOV-15
        1211 04-NOV-15
        1211 05-NOV-15
        1211 06-NOV-15
        1211 07-NOV-15
        1211 08-NOV-15
        1211 09-NOV-15
        1211 10-NOV-15
        1211 11-NOV-15
        1211 12-NOV-15
        1211 13-NOV-15
        1211 14-NOV-15
        1211 15-NOV-15
        1211 16-NOV-15
        1211 17-NOV-15
        1211 18-NOV-15
        1211 19-NOV-15
        1211 20-NOV-15

52 rows selected.

另一个查询将返回记录处于活动状态的日期：

with dates as
( select date '2015-10-25' + rownum as d
from dual
connect by level < 27
)
select distinct recordnumber, d
from   records r
       join dates
       on dates.d between r.startdate and nvl(r.stopdate, dates.d)
order by recordnumber, d;

RECORDNUMBER D
------------ ---------
        1012 26-OCT-15
        1012 27-OCT-15
        1012 28-OCT-15
        1012 29-OCT-15
        1012 30-OCT-15
        1012 31-OCT-15
        1012 01-NOV-15
        1012 02-NOV-15
        1012 03-NOV-15
        1012 04-NOV-15
        1012 05-NOV-15
        1012 06-NOV-15
        1012 07-NOV-15
        1012 08-NOV-15
        1012 09-NOV-15
        1012 10-NOV-15
        1012 11-NOV-15
        1012 12-NOV-15
        1012 13-NOV-15
        1012 14-NOV-15
        1012 15-NOV-15
        1012 16-NOV-15
        1012 17-NOV-15
        1012 18-NOV-15
        1012 19-NOV-15
        1211 28-OCT-15
        1211 29-OCT-15
        1211 30-OCT-15
        1211 31-OCT-15
        1211 01-NOV-15
        1211 02-NOV-15
        1211 05-NOV-15
        1211 06-NOV-15
        1211 07-NOV-15
        1211 08-NOV-15
        1211 09-NOV-15
        1211 10-NOV-15
        1211 11-NOV-15
        1211 12-NOV-15
        1211 13-NOV-15
        1211 14-NOV-15
        1211 15-NOV-15
        1211 16-NOV-15
        1211 17-NOV-15
        1211 18-NOV-15

45 rows selected.

结合这些，我们可以从所有日期中减去记录处于活动状态的日期，以获得记录处于非活动状态的日期：

with dates as
( select date '2015-10-25' + rownum as d
  from dual
  connect by level < 27
)
select recordnumber, d
from   records r
       cross join dates
minus
select recordnumber, d
from   records r
       join dates
       on dates.d between r.startdate and nvl(r.stopdate, dates.d)
order by recordnumber, d;

RECORDNUMBER D
------------ ---------
        1012 20-NOV-15
        1211 26-OCT-15
        1211 27-OCT-15
        1211 03-NOV-15
        1211 04-NOV-15
        1211 19-NOV-15
        1211 20-NOV-15