选择重叠日期的语句

Question

我需要一个SELECT查询，返回日期相互重叠的行的RoomID，例如。客户ID 10和客户ID 6在不同的日期到达，但他们在酒店逗留期间被分配到同一个房间。

RoomID ArrivalDate  DepartureDate ClientID
2      2020-11-02   2021-11-10    10
2      2021-11-01   2021-11-11    6
4      2021-10-18   2021-10-20    4
4      2021-12-13   2021-12-21    11
4      2021-12-14   2021-12-21    12
8      2021-12-10   2021-12-19    8
9      2021-09-20   2021-09-25    2
9      2021-09-21   2021-09-25    1
9      2021-12-10   2021-12-15    7
10     2021-10-19   2021-10-26    5
11     2021-10-02   2021-10-10    3
11     2021-12-12   2021-12-18    9
12     2021-10-04   2021-10-09    2

CREATE DATABASE Hotel;
CREATE TABLE reservations (
roomID INT NOT NULL,
ArrivalDate DATE NOT NULL,
DepartureDate DATE NOT NULL,
clientID INT NOT NULL,
PRIMARY KEY (roomID, ArrivalDate),
CHECK (ArrivalDate <= DepartureDate)
); 

谢谢你的帮助。

Answer 1

您可以使用exists:

获得重叠

select t.*
from t
where exists (select 1
from t t2
where t2.RoomID = t.RoomId and
t2.ClientID <> t.ClientId and
t2.ArrivalDate < t.DepartureDate and
t2.DepartureDate > t.ArrivalDate
);