我有两个如下表(日期格式:yyyy-MM-dd):
1) 表 1 - EMPLOYEE_OVERTIMES(别名: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)
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
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
最佳答案
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)
关于sql - 将两个表的第一个日期连接到第二个日期范围内，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14849304/