mysql酒店客房空房情况 [英] mysql hotel room availability

问题描述

我在Mysql中有一个可用性表，如下所示:

I have an availability table ind Mysql as follows:

id
room_id int(11)
avail_date date

对于每个房间，每个日期都有一行. 可能存在间隙，例如房间1可能有8月1,2,3,5,6,13,14,15的条目，隔天不可用.

For each room, there is a row for every date it is available. It is possible that there are gaps, for example room 1 might have entries for 1,2,3,5,6,13,14,15 of august, every other day it is not available.

我需要查询以找到在某个日期范围内每天可用的room_id列表.

I need a query to find a list of room_ids where there is availabilty for every day within a date range.

换句话说，获取room_id列表，其中每个房间在开始日期和结束日期之间的每个日期都有一个条目.

In other words, get a list of room_ids where there is an entry for each room for each date between startdate and enddate.

推荐答案

您想要这样的东西:

select room_id
from availability a
where avail_date between $start and $end
group by room_id
having count(*) = datediff($end, $start) + 1;

having子句正在计算该时间段内的行数，以查看它是否与所需的天数匹配.这是包容性"的逻辑，因此如果$start = $end，则假定您需要该日期的房间.

The having clause is counting the number of rows during that period to see if it matches the number of days you need. This is "inclusive" logic, so if $start = $end, then it assumes you need the room on that date.

这篇关于mysql酒店客房空房情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！