用于搜索房间可用性的 SQL 查询 [英] SQL query to search for room availability

问题描述

我有以下表格:

 CUSTOMERS (CustomerID, firstname, surname etc)
 ROOMS (RoomID, RoomType, Description)
 BOOKING (BookingID, CustomerID, Arrival Date, Departure Date, number of people, total cost)
 ROOMS_BOOKED (BookingID, RoomID)

用户输入到达和离开两个日期.然后检查房间的可用性.目前有一个房间被我自己插入到数据库中.所以 BOOKING 表现在包括 BookingID = 1，CustomerID = 1，Arrival Date = 24/03/2015，Departure Date = 26/03/2015，人数 = 1，总成​​本 = 40.00.然后在 ROOMS_BOOKED 表中，我有 BookingID = 1 和 RoomID = 2，这是一个标准房间.

The user enters two dates arrival and departure. Then checks for room availability. There is a room booked at the moment which I have inserted into the database myself. So the BOOKING table now includes BookingID = 1, CustomerID = 1, Arrival Date = 24/03/2015, Departure Date = 26/03/2015, number of people = 1, total cost = 40.00. Then in the ROOMS_BOOKED table I Have BookingID = 1 and RoomID = 2 which is a standard room.

我正在使用这个 sql 代码 -

I am using this sql code -

   SELECT RoomType FROM ROOMS WHERE RoomID NOT IN (SELECT RoomID FROM ROOMS_BOOKED) 

此查询返回尚未预订但不是我要查找的房间.例如，用户选择在数据库中预订的相同日期.因此，我希望能够在下一页显示所有房间类型，并为在所选日期预订的特定房间类型显示 x 或其他内容，类似于 www.galgorm.com.

This query is returning rooms that have not been booked, but is not what Im looking for. For example the user selects the same dates that are booked in the database. I therefore want to be able to display all room types on the next page and display an x or something for the particular room type that is booked on those selected dates, something similar to www.galgorm.com.

我需要这方面的帮助，每次我发布有关此的问题时，我都没有得到答案或某种形式的帮助.到目前为止，这个酒店系统的设计我已经完成了，我现在希望能够完成它.

I need help with this, everytime I post a question about this I never get an answer or some form of help. I have got so far with this hotel system with the design, I now want to be able to finish it off.

请帮帮我，我确定您有时也遇到同样的情况需要帮助.

Please help me, im sure sometimes you have been in the same situation needing help.

推荐答案

您有以下情况

The user's selected period:
--------[---------]-------
Booking no1 
[-----]-------------------
Booking no2
--------------------[----]
Booking no3
-----[----]---------------
Booking no4
-----------[---]----------
Booking no5
------[-------]-----------
Booking no6
--------------[--------]--
Booking no7
-----[----------------]---

您必须找出交叉的时期.显然情况 1 和 2 是免费的.案例 3、5、6 很容易捕捉，因为您可以搜索预订的开始日期或预订的结束日期是否在用户的选择范围内.在案例 4 和案例 7 中，您需要确定用户的选择日期是否在两次预订之间.

You will have to find which periods cross over. Obviously cases 1 and 2 are free. Cases 3,5,6 are easy to catch as you can search if either the start date of the booking or the end date of the booking is within the user's selection. Cases 4 and 7 you would need to find if either of the user's selection dates would be between the bookings.

所以以下找到空闲房间:

So the following finds free rooms:

DECLARE @ArrivalDate AS DATETIME
DECLARE @DepartureDate AS DATETIME

SELECT RoomType 
FROM ROOMS 
WHERE RoomID NOT IN 
(
    SELECT RoomID 
    FROM   BOOKING B
           JOIN ROOMS_BOOKED RB
               ON B.BookingID = RB.BookingID
    WHERE  (ArrivalDate <= @ArrivalDate AND DepartureDate >= @ArrivalDate) -- cases 3,5,7
           OR (ArrivalDate < @DepartureDate AND DepartureDate >= @DepartureDate ) --cases 6,6
           OR (@ArrivalDate <= ArrivalDate AND @DepartureDate >= ArrivalDate) --case 4
)

这篇关于用于搜索房间可用性的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！