select date1,date2,date3,date4 from table where (date1,date2) overlaps (date3,date4) 查询时间段date1-date2和时间段date3-date4 存在重叠的记录date1-date4必须是date型
SQL> select * from dual 2 where (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2006-01-01') 4 / no rows selected SQL> SQL> select * from dual 2 where (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2007-01-02') 4 / D - X SQL> SQL> select * from dual 2 where (date '2007-01-01', interval '5' year) 3 overlaps (date '2005-01-01', date '2007-01-02') 4 / D - X SQL> SQL> select * from dual 2 where (date '2007-01-01', interval '5' year) 3 overlaps (date '2005-01-01', interval '10' year) 4 / D - X SQL> SQL> select * from dual 2 where NOT (date '2007-01-01', date '2008-01-01') 3 overlaps (date '2005-01-01', date '2006-01-01') 4 / D - X SQL> SQL> SQL> with test_data as 2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 7 select * 8 from test_data t1, 9 test_data t2 10 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 11 / FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07 01-APR-07 01-AUG-07 01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07 FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08 15 rows selected. SQL> SQL> with test_data as 2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 7 select * 8 from test_data t1, 9 test_data t2 10 where NOT (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 11 / FROM_DT TO_DT FROM_DT TO_DT --------- --------- --------- --------- 01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07 10 rows selected. SQL> explain plan for 2 with test_data as 3 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all 4 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all 5 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all 6 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all 7 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual) 8 select * 9 from test_data t1, 10 test_data t2 11 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt) 12 / Explained. SQL> set linesize 200 SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- Plan hash value: 1640239735 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 22 (37)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | | 3 | UNION-ALL | | | | | | | 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 36 | 12 (0)| 00:00:01 | | 10 | VIEW | | 5 | 90 | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 | |* 12 | VIEW | | 1 | 18 | 2 (0)| 00:00:01 | | 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ ---------------------------------------- --------------------------------------------------- 12 - filter((INTERNAL_FUNCTION("T1"."FROM_DT"),INTERNAL_FUNCTION("T1"."TO_DT"))OVERLAPS(INTERNAL _FUNCTION("T2"."FROM_DT"),INTERNAL_FUNCTION("T2"."TO_DT"))) 26 rows selected.So not only does it let you check two pairs of dates to see if they overlap, it lets you check if they do not overlap (pretty simple stuff), and it also accepts a date and interval expression in place of date pairs.It doesn’t work for pairs of numbers though, it seems:SQL> select * from dual 2 where (1,5) 3 overlaps (3,8) 4 / where (1,5) * ERROR at line 2: ORA-00932: inconsistent datatypes: expected DATE got NUMBERSQL>
“select date1,date2,date3,date4 from table where (date1,date2) overlaps (date3,date4) 查询时间段date1-date2和时间段date3-date4 存在重叠的记录date1-date4必须是date型” 其中date1-date2 是不是一个时间段 如2009-9-9 到2010-9-9 是不是计算两个时间段有没有交汇的域?
查询时间段date1-date2和时间段date3-date4 存在重叠的记录date1-date4必须是date型
2 where (date '2007-01-01', date '2008-01-01')
3 overlaps (date '2005-01-01', date '2006-01-01')
4 /
no rows selected
SQL>
SQL> select * from dual
2 where (date '2007-01-01', date '2008-01-01')
3 overlaps (date '2005-01-01', date '2007-01-02')
4 /
D
-
X
SQL>
SQL> select * from dual
2 where (date '2007-01-01', interval '5' year)
3 overlaps (date '2005-01-01', date '2007-01-02')
4 /
D
-
X
SQL>
SQL> select * from dual
2 where (date '2007-01-01', interval '5' year)
3 overlaps (date '2005-01-01', interval '10' year)
4 /
D
-
X
SQL>
SQL> select * from dual
2 where NOT (date '2007-01-01', date '2008-01-01')
3 overlaps (date '2005-01-01', date '2006-01-01')
4 /
D
-
X
SQL>
SQL>
SQL> with test_data as
2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
7 select *
8 from test_data t1,
9 test_data t2
10 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
11 /
FROM_DT TO_DT FROM_DT TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07
01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07
01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07
01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07
01-APR-07 01-AUG-07 01-APR-07 01-AUG-07
01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07
01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07
FROM_DT TO_DT FROM_DT TO_DT
--------- --------- --------- ---------
01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08
01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08
01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08
01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08
15 rows selected.
SQL>
SQL> with test_data as
2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
7 select *
8 from test_data t1,
9 test_data t2
10 where NOT (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
11 /
FROM_DT TO_DT FROM_DT TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08
01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08
01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08
01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08
01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JAN-07 01-DEC-07
01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07
10 rows selected.
SQL> explain plan for
2 with test_data as
3 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
4 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
5 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
6 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
7 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
8 select *
9 from test_data t1,
10 test_data t2
11 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
12 /
Explained.
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1640239735
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 22 (37)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | UNION-ALL | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 36 | 12 (0)| 00:00:01 |
| 10 | VIEW | | 5 | 90 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 |
|* 12 | VIEW | | 1 | 18 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_EE7BE7EC | 5 | 90 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------
12 - filter((INTERNAL_FUNCTION("T1"."FROM_DT"),INTERNAL_FUNCTION("T1"."TO_DT"))OVERLAPS(INTERNAL
_FUNCTION("T2"."FROM_DT"),INTERNAL_FUNCTION("T2"."TO_DT")))
26 rows selected.So not only does it let you check two pairs of dates to see if they overlap, it lets you check if they do not overlap (pretty simple stuff), and it also accepts a date and interval expression in place of date pairs.It doesn’t work for pairs of numbers though, it seems:SQL> select * from dual
2 where (1,5)
3 overlaps (3,8)
4 /
where (1,5)
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected DATE got NUMBERSQL>
查询时间段date1-date2和时间段date3-date4 存在重叠的记录date1-date4必须是date型”
其中date1-date2 是不是一个时间段 如2009-9-9 到2010-9-9
是不是计算两个时间段有没有交汇的域?