Oracle SQL，基于日期重叠分割日期

Question

我有两个表EMPLOYEE, COMPANY

EMPID  START_DATE  END_DATE
123    1/05/2015   30/05/2015


COMPANYID   EMPID  START_DATE   END_DATE    SALARY
001          123    7/05/2015   15/05/2015  10000

我需要写一个查询，所以结果是这样的，

COMPANYID  EMPID   START_DATE  END_DATE   SALARY
001         123    1/05/2015   7/05/2015  0
001         123    7/05/2015   14/05/2015 10000
001         123    14/05/2015  30/05/2015 0

同样，该查询应该适用于所有类型的重叠——部分重叠和完全重叠。请帮助。

Answer 1

一种选择是将时间线分成几个部分，并拆分所有可能性:

1. 员工入职日期与公司入职日期重叠
2. 从员工开始日期到员工结束日期，以防他们不重叠
3. 从公司开始日期到员工结束日期，如果员工结束日期在公司结束日期之前
如果员工结束日期在公司结束日期之后，则从公司开始日期到公司结束日期的
4. 等。

然后你可以用"union all"将所有查询连接在一起，这将导致这样的查询(我可能忘记了一些可能性):

select c.companyid
, e.empid
, e.start_date
, e.end_date
, 0
from company c
,    employee e
where e.empid = c.empid
and   e.end_date <= c.start_date
union all
select c.companyid
, e.empid
, e.start_date
, c.start_date
, 0
from company c
,    employee e
where e.empid = c.empid
and  e.start_date <= c.start_date 
and  e.end_date > c.start_date
union all
select c.companyid
, e.empid
, c.start_date
, e.end_date
, c.salary
from company c
,    employee e
where e.empid = c.empid
and  e.start_date <= c.start_date 
and  e.end_date > c.start_date
and  e.end_date <= c.end_date
union all
select c.companyid
, e.empid
, c.start_date
, c.end_date
, c.salary
from company c
,    employee e
where e.empid = c.empid
and  e.start_date <= c.start_date 
and  e.end_date > c.end_date
union all
select c.companyid
, e.empid
, c.end_date
, e.end_date
, 0
from company c
,    employee e
where e.empid = c.empid
and  e.start_date <= c.start_date 
and  e.end_date > c.end_date

，结果将是:

companyid   empid   start_date  end_date    salary
1            123    5/1/2015    5/7/2015    0
1            123    5/7/2015    5/15/2015   10000
1            123    5/15/2015   5/30/2015   0