MySql查询查找给定日期“从"和“到"的可用房间 [英] MySql query to find rooms available given dates 'From' and 'to'

问题描述

表如下

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

Given the arrival(From_D) and departure(To_D) dates how do i find out the id of the rooms that are available

我尝试过:

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

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

But seeing the structure of my DB problem occurs if there multiple booking of a room stored in the DB, and it books the room even if it was not free in that range of dates

推荐答案

请考虑以下图表(抱歉，手写不良):

Consider the following diagram (sorry for bad handwriting):

在上图中，我们可以在水平轴上看到日期范围. From_D至To_D表示特定房间的预订插槽.考虑了四种情况的到达和离开日期(分别以A _i 和D _i 表示).

In the above diagram, we can see date ranges on a horizontal axis. From_D to To_D represents a booked slot for a specific room. Four cases are considered for Arrival and Departure dates (denoted by A_i and D_i).

我们可以清楚地看到，当到达和离开日期完全在预订的位子的左侧或右侧时，特定房间仅 可用.

We can clearly see that a particular room is available only when the arrival and departure dates are either completely on the left side, or the right side of the booked slot.

我们可以在RoomID上选择GROUP BY，并使用HAVING子句仅考虑那些预订符合上述标准的房间.

We can GROUP BY on RoomID and consider only those rooms, where every booking for follows the criteria defined above, using the HAVING clause.

查询将仅是以下内容:

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(*)

---

DB Fiddle上的演示

---

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月3日之间是否有空

Query: Check for any availability between 2018-11-01 and 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可用

Result: Only RoomId 4 is available as per the sample data

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

这篇关于MySql查询查找给定日期“从"和“到"的可用房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！