mysql - 酒店预订检查抵达日期和离开日期之间的空房吗？

Question

基本上，我从房间表中检索所有可用房间以及到达和离开日期。

SQL 查询

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-31'

结果是

id day        roomname avaroom
 1 2016-08-25 RoomA          0
 2 2016-08-26 RoomA          2
 3 2016-08-27 RoomA          0
 4 2016-08-28 RoomA          1
 5 2016-08-29 RoomA          1
 6 2016-08-30 RoomA          0
 7 2016-08-31 RoomA          1

如果到达日期和离开日期之间 avaroom 不等于 0，我只想要可用的房间

我想要结果

SQL查询

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-31'

它必须是空结果

SQL查询

 SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-25' and '2016-08-26'

它必须是空结果

SQL查询

 SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-26' and '2016-08-27'

它显示一个结果

SQL查询

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-26' and '2016-08-28'

结果必须为空

SQL查询

SELECT * FROM `roomcalendar` where day BETWEEN '2016-08-28' and '2016-08-30'

显示两个结果

谢谢。

Answer 1

I just want available room if avaroom not equal to 0 beteen arrival date and departure date

因此，如果您重新表述:

• 如果两个日期之间没有 avaroom = 0 的行，则您需要该房间。

翻译成 SQL:

select * from roomcalendar
where day between '2016-08-25' and '2016-08-31'
and not exists (
  select * from roomcalendar
  where day between '2016-08-25' and '2016-08-31'
  and avaroom = 0
);

您还可以通过以下方式重新表述:

• 如果 avaroom 不为 0 的行数是预期的总行数(在本例中，这对应于天数)，则您需要该房间

翻译成 SQL:

select * from roomcalendar
where day between '2016-08-25' and '2016-08-31'
and avaroom != 0
having count(*) = datediff('2016-08-31','2016-08-25') + 1

您可能需要稍微调整查询，但这应该会为您指明正确的方向。