MySql 查询查找给定日期 'From' 和 'to' 可用的房间

Question

表格如下

rooms(RoomId(PK)(int),
      RoomName(varchar))

bookings(RoomId(FK)(int),
         From_D(date),
         To_D(date),
         Bookee(varchar))

(PK-primary Key ,FK-foreign Key)

鉴于到达(From_D)和出发(To_D)日期，我如何找到可用房间的ID

我尝试过:

SELECT DISTINCT RoomId 
FROM bookings 
WHERE  arrival not in(From_D,To_d) AND 
       departure not in(From_D,To_d)

但是，如果数据库中存储了多个房间预订，并且即使该房间在该日期范围内不空闲，它也会预订房间，那么看到我的数据库结构就会出现问题

Answer 1

考虑下图(抱歉，笔迹不好):

在上图中，我们可以在水平轴上看到日期范围。 From_D 到 To_D 表示特定房间的预订时段。到达和出发日期考虑四种情况(用 A_i 和 D_i 表示)。

我们可以清楚地看到，只有当到达和离开日期完全位于预订时段的左侧或右侧时，特定房间才有空位。

我们可以使用 RoomID 进行GROUP BY，并仅考虑那些每次预订都遵循上面定义的标准的房间，使用 HAVING 子句.

查询如下:

SELECT RoomID 
FROM bookings 
GROUP BY RoomID 
HAVING 
  SUM((From_D < :arrival AND To_D < :arrival) 
             OR 
      (From_D > :departure AND To_D > :departure)) = COUNT(*)

<小时/>

Demo on DB Fiddle

CREATE TABLE `rooms` (
 `RoomId` int(4) NOT NULL,
 `RoomName` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`RoomId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `bookings` (
 `RoomId` int(4) NOT NULL,
 `From_D` date NOT NULL,
 `To_d` date NOT NULL,
 `B_Name` varchar(20) NOT NULL,
 KEY `RoomId` (`RoomId`),
 CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`RoomId`) REFERENCES `rooms` (`RoomId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `rooms` (`RoomId`, `RoomName`) VALUES 
('1', 'Auditorium'), 
('2', 'Room2'), 
('3', 'Room3'), 
('4', 'Room4'), 
('5', 'Room5');

INSERT INTO `bookings` (`RoomId`, `From_D`, `To_d`, `B_Name`) VALUES 
('1', '2018-11-01', '2018-11-03', 'Trance'), 
('2', '2018-11-02', '2018-11-07', 'Alcoding'), 
('3', '2018-11-01', '2018-11-04', 'DebSoc'), 
('4', '2018-11-12', '2018-11-17', 'MunSoc'), 
('5', '2018-11-03', '2018-11-06', 'Pulse');

查询:检查 2018 年 11 月 1 日至 2018 年 11 月 03 日期间是否有空房

SELECT RoomId 
FROM bookings 
GROUP BY RoomID 
HAVING 
  SUM((From_D < '2018-11-01' AND To_D < '2018-11-01') 
             OR 
      (From_D > '2018-11-03' AND To_D > '2018-11-03')) = COUNT(*)

结果:根据示例数据，仅 RoomId 4 可用

| RoomId |
| ------ |
| 4      |