酒店预订系统 SQL 查询:确定某个特定房间何时可用

我有一个酒店预订系统的查询，我需要查找特定房间可用的日期.(这是一家精品酒店，人们在那里预订特定房间，因此他们在获得此代码之前就知道他们想要的确切房间.我所追求的结果是 ONE 房间的详细信息，即我在查询中指定的房间 -- 我不是在寻找多个房间的信息.)

可用性"表架构很简单，每一行是:

room_iddate_occupied - 被占用的一天(如2011-01-01")

因此，例如，如果某个房间在 1 月 1 日至 1 月 5 日期间有人入住，则会在可用性表中添加五行，该房间被占用的每一天都有一行.

这是我正在努力解决的问题:

• 用于查找特定房间在开始日期和结束日期之间何时可用的查询，类似于:

<块引用>

选择房间.* 从房间，可用性哪里房间.id = 123AND 可用性.room_id = rooms.id并且 start_date 和 end_date 之间没有任何内容可用.date_occupied

• 我也在寻找类似的查询，我只想查看在开始日期和接下来的两天内是否有特定的房间可用，例如:

<块引用>

选择房间.* 从房间，可用性哪里房间.id = 123AND 可用性.room_id = rooms.id并且 start_date、start_date+1day 和 start_date+2days 不可用.date_occupied

我在试图找出确切的连接时有点卡住了.有什么建议?请注意，如果有帮助，我对可用性表的不同架构完全开放.无论什么使查询最有效.

解决方案

我认为最好的方法是简单地运行一个查询来查看房间是否被占用，并在没有返回任何行的情况下指示可用性.

因此，对于您的第一个查询:

SELECT COUNT(id) AS occupied_days FROM 可用性哪里 room_id = 123AND date_occupied BETWEEN @start_date AND @end_date;

如果 occupied_days == 0 则房间可用.

对于您的第二个查询，只需将 @end_date 替换为 DATE_ADD(@start_date, @num_days)

只是对涉及连接的答案的一些评论:由于您将搜索限制为特定的 room_id，因此将 availability 表连接到 room 表，因为它没有提供任何必要的信息.所有这些 LEFT JOIN 只会使查询复杂化，可能影响性能并且没有任何用处.

此外，虽然您可能不喜欢搜索占用率然后推断可用性的方法，但我猜想这个查询是迄今为止查询引擎优化最快和最容易的，因为它基于单个列，如果索引，会使事情变得更快.

I have a query for a hotel reservation system and I need to find dates when a specific room is available. (It's a boutique hotel where people reserve specific rooms, so they know the exact room they want before they get to this code. The result I'm after is the detail for ONE room, the one I specify in the query-- I am not looking for info on multiple rooms.)

The 'availability' table schema is simple, each row is:

room_id
date_occupied - a single day that is occupied (like '2011-01-01')

So, if, for example, a room is occupied from January 1 to January 5, five rows are added to the availability table, one for each day the room is occupied.

Here's what I'm trying to work out:

• the query to find when a specific room is available between a start and end date, sort of like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND nothing between start_date and end_date is in availability.date_occupied

• I'm also seeking a similar query where I just want to see if a specific room is available for the start date and the following two days, something like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND start_date, start_date+1day and start_date+2days is not in availability.date_occupied

I'm a bit stuck trying to figure out the exact joins. Any suggestions? Note that if it helps, I'm totally open to a different schema for the availability table. Whatever makes the queries work most efficiently.

解决方案

I think the best way to do this is to simply run a query to see if a room is occupied and indicate availability in the event that no rows are returned.

As such, for you first query:

SELECT COUNT(id) AS occupied_days FROM availability
WHERE room_id = 123
      AND date_occupied BETWEEN @start_date AND @end_date;

If occupied_days == 0 then the room is available.

For your second query, just replace @end_date with DATE_ADD(@start_date, @num_days)

Just some comments on answers involving a join: since you're limiting your search to a specific room_id, it makes no sense to join the availability table to the room table, since it's providing no necessary information. All these LEFT JOINs are just complicating the query, potentially impairing performance and providing nothing of any use.

Also, while you may baulk at the approach of searching for occupancy and then inferring availability, I would guess that this query is by far the fastest and easiest for the query engine to optimize, since it's based on a single column which, if indexed, will make things even faster.