如何找到第一间在连续预订之间有2天免费空位的房间
如何找到第一间在连续预订之间有2天免费空位的房间
提问于 2016-11-08 05:36:24
我有这样的桌子：
room_id date_reservartion
101 October, 01 2016
101 October, 03 2016
102 October, 02 2016
102 October, 05 2016
103 October, 01 2016
103 October, 02 2016
103 October, 04 2016
104 October, 04 2016 我试着找第一间免费的房间，连续两天免费。
在这种情况下，答案是102号房间，因为预订2-10-2016和5-10-2016，3-10和4-10是免费的。你能帮我一下吗？
链接到SQL Fiddle
回答 4
Stack Overflow用户
发布于 2016-11-08 05:48:18
请尝尝这个。
Select top 1 * from (
Select a.room_id,DATEDIFF(DAY,a.date_reservartion,b.date_reservartion)-1 as Diff
FROM room as a
INNER JOIN room as b on a.room_id=b.Room_id
) as t Where Diff = 2
Order by room_id它显示102见..。
Stack Overflow用户
发布于 2016-11-08 05:59:30
尝尝这个
Select top 1 a.room_id,DATEDIFF(DAY,a.date_reservartion,b.date_reservartion)-1 as Diff
FROM room as a
INNER JOIN room as b on a.room_id=b.room_id Where Diff = 2 Order by a.room_idStack Overflow用户
发布于 2016-11-08 06:10:23
在2016-10-05年以后，每个房间都是免费的，这里有一个sql，它显示了当房间有2天的空闲时间。您可以轻松地修改它以查看任何空闲周期(+间隔N天)。如果您应用限制1，您将看到第一个免费房间。
select
room_id,
date_reservartion + interval 1 day AS from_date,
date_reservartion + interval 2 day AS to_date
from booking as b
where not exists (
select 1
from booking as b2
where b2.room_id = b.room_id
and b2.date_reservartion >= b.date_reservartion + interval 1 day
and b2.date_reservartion <= b.date_reservartion + interval 2 day
)
order by from_date ASC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40479711
复制相关文章
相似问题
腾讯云开发者
