本文介绍了查找免费房间(预订系统)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
对于预订系统,我有一张桌子,上面有房间,到达和离开的时间.
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 ...)
找到免费房间
这篇关于查找免费房间(预订系统)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!