酒店预订检查到达日期和离开日期之间的空房间?

问题描述

基本上，我会从房间表中检索所有具有到达日期和离开日期的房间.

Basically i retrieve all available room with arrival and departure date from the room table.

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

如果空位不等于0到达日期和离开日期，我只想要空房间

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

我想要结果

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'

它显示两个结果

谢谢.

推荐答案

因此，如果您改写此内容:

So if you reformulate this:

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

• you want the room if there is no row where avaroom = 0 between two dates.

用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
);

您也可以这样说:

• 如果空闲空间不为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

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

You might have to tweak the queries a bit, but that should point you to the right direction.

这篇关于酒店预订检查到达日期和离开日期之间的空房间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！