mysql - MySql 查询以查找给定日期“从”和“到”可用的房间
问题描述
表格如下
rooms(RoomId(PK)(int),
RoomName(varchar))
bookings(RoomId(FK)(int),
From_D(date),
To_D(date),
Bookee(varchar))
(PK-primary Key ,FK-foreign Key)
给定日期arrival(From_D)
和departure(To_D)
日期,我如何找出可用房间的 ID
我试过:
SELECT DISTINCT RoomId
FROM bookings
WHERE arrival not in(From_D,To_d) AND
departure not in(From_D,To_d)
但是,如果在数据库中存储了多个房间的预订,并且即使在该日期范围内它不是免费的,它也会预订房间,那么就会出现我的数据库问题的结构
解决方案
考虑下图(对不起,笔迹不好):
在上图中,我们可以在横轴上看到日期范围。From_D
toTo_D
表示为特定房间预订的空档。到达和离开日期(用 A i和 D i表示)考虑了四种情况。
我们可以清楚地看到,只有当到达和离开日期完全在预订时段的左侧或右侧时,特定房间才可用。
我们可以GROUP BY
使用该条款RoomID
仅考虑并考虑那些每次预订都遵循上述标准的房间。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(*)
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-01 和 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 |
推荐阅读
- reactjs - 未在浏览器中显示图像 - React
- angular - 改善使用授权代码流时 SPA 的缓慢初始页面加载
- flutter - 何时使用缓存网络图像
- java - 仅在 Spring Boot 中的 HTTPS 请求
- .net - 无法读取未定义 Angular 9 的属性“类型”
- excel - 不使用 HTML 代码在电子邮件中发送嵌入图像
- mysql - 合并找到重复项的行
- node.js - 如何使用 Nodejs 和 Mongoose 将请求正文数组发布到 MongoDb
- dictionary - 给定 Kotlin 中的键列表对地图进行切片
- docker - 如何将 .env 文件传递给 Go 项目中的 docker 映像?