一个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请大家告诉我,我哪里写错了
表结构:
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请大家告诉我,我哪里写错了
这里
改成
b.arrival_date between a.arrival_date and a.leave_date
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