一个sql很简单的求时间重叠数据的问题

[davidleee]

一个sql求时间重叠数据的问题：
表结构：
Name Type Nullable Default Comments
------------ -------- -------- ------- --------
ROOM_NBR INTEGER
ARRIVAL_DATE DATE
LEAVE_DATE DATE
GUEST_NAME CHAR(30) Y

表内数据：
ROOM_NBR ARRIVAL_DATE LEAVE_DATE GUEST_NAME
1 4/1/2010 4/2/2010 A
1 4/3/2010 4/4/2010 B
2 4/1/2010 4/3/2010 C
2 4/2/2010 4/4/2010 D

现在要求求出哪个房间存在时间重叠的
希望结果：
ROOM_NBR
2

我现在的sql是：
select a.room_nbr from test_hotel a where exists
(
select 1 from test_hotel b where
a.room_nbr=b.room_nbr and
(a.arrival_date<=b.leave_date or a.leave_date>=b.arrival_date)
)

但出来的结果是
1
1
2
2

请大家告诉我，我哪里写错了

[you_tube]

(a.arrival_date<=b.leave_date or a.leave_date>=b.arrival_date)
这里

[you_tube]

改成
b.arrival_date between a.arrival_date and a.leave_date

[Adebayor]

SQL> with tt as(
2 select 1 ROOM_NBR,to_date('20100401','yyyy-mm-dd') ARRIVAL_DATE, to_date('20100402','yyyy-mm-dd') LEAVE_DATE,'A' GUEST_NAME from dual
3 union all select 1, to_date('20100403','yyyy-mm-dd'), to_date('20100404','yyyy-mm-dd'), 'B' from dual
4 union all select 2, to_date('20100401','yyyy-mm-dd'),to_date('20100403','yyyy-mm-dd'),'C' from dual
5 union all select 2,to_date('20100402','yyyy-mm-dd'),to_date('20100404','yyyy-mm-dd'),'D' from dual
6 )select ROOM_NBR from(
7 select tt.*,lag(LEAVE_DATE,1) over(partition by room_nbr order by rownum) a from tt
8 )b where a between b.ARRIVAL_DATE and b.LEAVE_DATE;

ROOM_NBR
----------
2

[Sunkien]

把a.arrival_date<=b.leave_date or a.leave_date>=b.arrival_date

改为 a.arrival_date > b.arrival_date and a.arrival_date < b.leave_date

[小灰狼W]

发一个帖就行了，不要每个小版发一次...