本文介绍了将两个表的日期从第一开始联接，将日期范围从第二开始联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个如下表(日期格式: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
推荐答案
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)
这篇关于将两个表的日期从第一开始联接，将日期范围从第二开始联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！