查找免费房间(预订系统) [英] Find free room (booking system)

问题描述

对于预订系统，我有一张桌子，上面有房间，到达和离开的时间.

For a booking system I have a table with rooms, arrival and departure.

示例数据:

id | room | arrival    | departure
---+------+------------+-----------
1  | 1    | 2011-03-12 | 2011-03-14
2  | 1    | 2011-03-08 | 2011-03-09
3  | 1    | 2011-03-19 | 2011-03-20
4  | 2    | 2011-03-22 | 2011-03-30 
5  | 2    | 2011-03-19 | 2011-03-22

现在的问题是:如果我有新预订(从2011-03-10到2011-03-12)，如何查看哪些房间是免费的?

My question is now: if I have a new booking (2011-03-10 to 2011-03-12), how can I check which rooms are free?

此示例的输出应为房间1和2.

Output for this example should be room 1 and 2.

推荐答案

以下是一个查询，该查询将显示日期跨度的非免费房间:

Here is a query that will show the NOT-FREE rooms for a date span:

select room from bookings where
(arrival<'2011-03-12' and departure>='2011-03-12') -- overlap at the end
OR (arrival<='2011-03-10' and departure>'2011-03-10') -- overlap at the start
OR (arrival>='2011-03-10' and departure<='2011-03-12') -- complete overlap

您可以将其与

select roomnumber from rooms where roomnumber not in (... as above ...)

找到免费房间

这篇关于查找免费房间(预订系统)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！