mysql 客房,mysql酒店客房空房情况

I have an availability table ind Mysql as follows:

id

room_id int(11)

avail_date date

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.

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

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

解决方案

You want something like this:

select room_id

from availability a

where avail_date between $start and $end

group by room_id

having count(*) = datediff($end, $start) + 1;

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.