将两个表的日期从第一开始联接，将日期范围从第二开始联接 [英] Join two tables date from first in range of dates from second

问题描述

我有两个如下表(日期格式:yyyy-MM-dd):
1)表1-EMPLOYEE_OVERTIMES(别名:EO)

I have two tables like below (date format: yyyy-MM-dd):
1) Table1 - EMPLOYEE_OVERTIMES (alias: EO)

EMPLOYEE_ID | OVERTIME_DATE
------------------------------------------------
1           | 2012-04-01
2           | 2012-08-14
3           | 2012-07-22
4           | 2012-10-30
5           | 2012-06-07

2)表2-EMPLOYEE_HOLIDAYS(别名:EH)

2) Table2 - EMPLOYEE_HOLIDAYS (alias: EH)

EMPLOYEE_ID | START_DATE | END_DATE   |
-----------------------------------------
1           | 2012-03-28 | 2012-04-10
2           | 2012-01-14 | 2012-01-30
3           | 2012-07-15 | 2012-07-25
4           | 2012-10-10 | 2012-10-13
5           | 2012-06-01 | 2012-06-07

表EMPLOYEE_OVERTIMES和EMPLOYEE_HOLIDAYS是从其他表联接的.我想找到所有符合以下条件的记录: EH.START_DATE< = EO.OVERTIME_DATE< = EH.END_DATE

The tables EMPLOYEE_OVERTIMES and EMPLOYEE_HOLIDAYS are joined from other tables. I would like to find all records that meet folowing criteria: EH.START_DATE <= EO.OVERTIME_DATE <= EH.END_DATE

3)结果表

EMPLOYEE_ID | START_DATE | END_DATE   | OVERTIME_DATE
-------------------------------------------------------
1           | 2012-03-28 | 2012-04-10 | 2012-04-01
3           | 2012-07-15 | 2012-07-25 | 2012-07-22
5           | 2012-06-01 | 2012-06-07 | 2012-06-07

推荐答案

SQLFiddle演示

SELECT EH.*, EO.OVERTIME_DATE 
FROM EMPLOYEE_HOLIDAYS EH
JOIN EMPLOYEE_OVERTIMES EO
ON (EO.EMPLOYEE_ID = EH.EMPLOYEE_ID) AND 
   (EO.OVERTIME_DATE BETWEEN EH.START_DATE AND EH.END_DATE)

这篇关于将两个表的日期从第一开始联接，将日期范围从第二开始联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！