选择Oracle的表中缺少日期范围

Question

i具有以下数据表数据为：

员工Daydate-------------------------------------------------101 01/08/2017101 02/08/2017101 04/08/2017101 06/08/2017101 07/08/2017102 01/08/2017102 03/08/2017102 06/08/2017

我想编写一个查询以获取以下丢失的日期2017年1月8日至07/08/2017数据为：

员工Daydate-------------------------------------------------101 03/08/2017101 05/08/2017101 07/08/2017102 02/08/2017102 04/08/2017102 05/08/2017102 07/08/2017

如何处理？！

Answer 1

使用我认为更好的日期表，或者使用派生表来填充日期（Google下的许多代码在" Oracle"下" Oracle如何生成日期"）：

SELECT t.employeeid,s.date
FROM (SELECT distinct p.employeeid FROM YourTable p) t
CROSS JOIN ( DATE TABLE  Derived Table) s

此查询将为每个员工生成整个日期范围。如果您只想要那些缺少的人：

SELECT t.employeeid,s.date
FROM (SELECT distinct p.employeeid FROM YourTable p) t
CROSS JOIN ( DATE TABLE  Derived Table) s
LEFT JOIN YourTable h
 ON(h.employeeID = t.employeeId and h.date = s.date)
WHERE h.employeeID IS NULL

Answer 2

您可以使用层次查询生成所有行：

SELECT DATE '2017-08-01' + LEVEL - 1 AS dt
FROM   DUAL
CONNECT BY DATE '2017-08-01' + LEVEL - 1 <= DATE '2017-08-07'

或递归的子查询条款条款：

WITH alldates ( dt ) AS (
  SELECT DATE '2017-08-01' FROM DUAL
UNION ALL
  SELECT dt + 1 FROM alldates WHERE dt < DATE '2017-08-07'
)
SELECT * FROM alldates

然后，您可以使用分区的外部连接将其与现有数据NAD过滤器组合为那些缺少的行：

WITH alldates ( dt ) AS (
  SELECT DATE '2017-08-01' FROM DUAL
UNION ALL
  SELECT dt + 1 FROM alldates WHERE dt < DATE '2017-08-07'
)
SELECT employeeId,
       dt
FROM   alldates d
       LEFT OUTER JOIN
       your_table t
       PARTITION BY ( t.employeeID )
       ON ( t.daydate = d.dt )
WHERE  daydate IS NULL;

Answer 3

假设您有一个员工，员工是主要钥匙 - 并假设可能有一些员工根本不出现在您的data_table中，则必须将其包含在您的输出：

select e.employeeid, d.daydate
from   employee e cross join ( select date '2017-08-01' + level - 1 as daydate
                                 from dual
                                 connect by level <= 7
                             ) d
where  (e.employeeid, d.daydate) not in (select employeeid, daydate from data_table)
;