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

Question

我是初学者，正在学习 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)

表名 - 记录

一条记录从它的开始日期到停止日期是活跃的。如果 StopDate 为 null，则表示该记录仍处于事件状态。考虑到最后一个 StopDate 是终止日期，我对在 Oracle 中编写查询查找记录处于非事件状态的日期一无所知。

输出应该有点像:

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    

如有任何帮助，我们将不胜感激。谢谢。

Answer 1

首先，您需要一种生成所有感兴趣日期的方法 - 例如从 26-Oct-15 到 20-Nov-15 的所有日期(因为这是您在示例输出中使用的范围)。此查询将执行此操作:

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

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

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