mysql - 如何向已经复杂的查询添加一张表

Question

我正在尝试构建酒店客房可用性的搜索查询，但似乎他的查询超出了我的能力范围，我需要帮助来构建它。请注意，数据库中将会有多个酒店。

即使我正在寻找可用房间，我的想法也不是建立可用性表，而是使用预订表，我假设如果房间不在预订表中，则它是可用的。

我的搜索表单中有以下字段:

面积(用areaid表示)、checkInDate、checkOutDate、房间(他需要多少个房间)、成人和 child 。

以下是此搜索中应涉及的表:

房间类型预订房间预订和酒店

(对于那些困惑为什么我有预订房间的人来说，原因很简单，一个预订可以有多个房间，所以它是辅助表)

以下是表格:

CREATE TABLE `room` (
    `roomID` int(11) NOT NULL AUTO_INCREMENT,
    `hotelID` int(11) NOT NULL,
    `roomtypeID` int(11) NOT NULL,
    `roomNumber` int(11) NOT NULL,
    `roomName` varchar(255) NOT NULL,
    `roomDescription` text,
    `roomVisible` tinyint(4) NOT NULL,
    PRIMARY KEY (`roomID`),
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

CREATE TABLE `roomtype` (
    `roomtypeID` int(11) NOT NULL AUTO_INCREMENT,
    `hotelID` int(11) NOT NULL,
    `roomtypeName` varchar(255) NOT NULL,
    `roomtypeAdults` int(11) NOT NULL,
    `roomtypeChildrens` int(11) NOT NULL,
    `roomtypeDescription` text,
    PRIMARY KEY (`roomtypeID`),
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

CREATE TABLE `hotel` (
    `hotelID` int(11) NOT NULL AUTO_INCREMENT,
    `areaID` int(11) NOT NULL,
    `hotelcategoryID` int(11) DEFAULT NULL,
    `hotelName` varchar(255) NOT NULL,
    `hotelShortDescription` text,
    `hotelAddress` varchar(255) DEFAULT NULL
    PRIMARY KEY (`hotelID`),
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;


CREATE TABLE `reservation` (
    `reservationID` int(11) NOT NULL AUTO_INCREMENT,
    `customerID` int(11) NOT NULL,
    `hotelID` int(11) NOT NULL,
    `reservationCreatedOn` datetime NOT NULL,
    `reservationCreatedFromIp` varchar(255) CHARACTER SET greek NOT NULL,
    `reservationNumberOfAdults` tinyint(4) NOT NULL,
    `reservationNumberOfChildrens` tinyint(4) NOT NULL,
    `reservationArrivalDate` date NOT NULL,
    `reservationDepartureDate` date NOT NULL,
    `reservationCustomerComment` text CHARACTER SET greek,
    PRIMARY KEY (`reservationID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `reservationroom` (
    `reservationroomID` int(11) NOT NULL AUTO_INCREMENT,
    `reservationID` int(11) NOT NULL,
    `hotelID` int(11) NOT NULL,
    `roomID` int(11) NOT NULL,
    PRIMARY KEY (`reservationroomID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

请注意，我已从表中删除了不必要的字段，以使代码更短且更易于阅读。

此时，我有以下有效的查询，但问题是我必须在此查询中包含预订表， 请记住，我的搜索有 2 个字段 checkInDate、checkOutDate，它们是检查哪些酒店有可用房间的主要参数。

这是当前查询:

    SELECT r.*, h.*,rr.* FROM room r 
LEFT JOIN `reservationroom` rr 
ON r.`hotelID` = rr.`hotelID` AND r.`roomID` = rr.`roomID`
LEFT JOIN `hotel` h
ON h.`hotelID` = r.`hotelID` 
WHERE ( rr.`reservationroomID` = '' OR rr.`reservationroomID` IS  NULL );

任何人都可以帮助我在此查询中添加预订表吗？

问候，约翰

Answer 1

主要问题是从搜索中消除房间，因为它们已被预订。为此，您需要加入预订表并检查日期范围是否与预订的范围重叠。

其基本原理是:-

SELECT r.*, h.*,rr.* 
FROM room r 
INNER JOIN `hotel` h
ON h.`hotelID` = r.`hotelID` 
LEFT JOIN `reservationroom` rr 
ON r.`hotelID` = rr.`hotelID` 
AND r.`roomID` = rr.`roomID`
LEFT JOIN reservation res
ON  rr.reservationID = res.reservationID
AND res.reservationArrivalDate < $checkOutDate
AND res.reservationDepartureDate > $checkInDate
WHERE ( rr.`reservationroomID` = '' 
OR rr.`reservationroomID` IS  NULL );

当您需要检查可用房间数量时，情况会变得更加复杂。为此，您可能需要使用与上面类似的内容作为子查询，获取酒店 ID 和房间数，然后使用 HAVING 检查计数是否大于或等于所需的房间数，然后加入结果对照酒店和房间，获取可用房间所需的详细信息。

编辑 - 更多细节(未测试)。子查询获取在所需时间内有足够空闲房间的酒店，然后连接回酒店和房间以获取这些酒店的详细信息。

SELECT r.*, h.*
FROM room r 
INNER JOIN hotel h
ON h.hotelID = r.hotelID 
INNER JOIN
(
    SELECT h.hotelID, COUNT(r.roomID) AS RoomCount
    FROM room r 
    INNER JOIN hotel h
    ON h.hotelID = r.hotelID 
    LEFT JOIN reservationroom rr 
    ON r.hotelID = rr.hotelID 
    AND r.roomID = rr.roomID
    LEFT JOIN reservation res
    ON  rr.reservationID = res.reservationID
    AND res.reservationArrivalDate < $checkOutDate
    AND res.reservationDepartureDate > $checkInDate
    WHERE ( res.reservationID IS  NULL )
    AND h.areaID = $areaID
    GROUP BY h.hotelID
    HAVING RoomCount >= $rooms
) sub0
ON h.hotelID = sub0.hotelID