酒店预订系统SQL查询:确定何时可以使用一个特定的房间 [英] hotel reservation system SQL query: identify when one specific room is available

问题描述

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

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

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

因此，例如，如果从1月1日到1月5日有一个房间被占用，则将五行添加到可用性表中，该房间每天被占用一行.

这就是我要解决的问题:

• 用于查询在开始日期和结束日期之间何时有特定房间的查询，例如:

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

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

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

我有点想找出确切的联接.有什么建议?请注意，如果有帮助，我完全可以使用可用性表的其他模式.无论哪种方式都能使查询最有效地工作.

我认为，做到这一点的最佳方法是简单地运行查询以查看是否有房间，并在不返回任何行的情况下指示可用性.

这样，对于您的第一个查询:

SELECT COUNT(id) AS occupied_days FROM availability
WHERE room_id = 123
      AND 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.

这篇关于酒店预订系统SQL查询:确定何时可以使用一个特定的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！