mysql - 使用 mySQL 查找今天的免费房间

Question

如果在 mysql 表 RESERVATIONS 中有 RPOM_NUMBER、DATE_ARRIVAL 和 DATE_DEPARTED

如何找到今天可用的所有房间？

Answer 1

检查在特定时间可用的房间:

select RPOM_NUMBER
from RESERVATIONS r
group by RPOM_NUMBER
having not exists (
    select *
    from RESERVATIONS 
    where
       RPOM_NUMBER = r.RPOM_NUMBER and
       now() between DATE_ARRIVAL and DATE_DEPARTED
)

检查在给定时间段(从start_time 到end_time)可用的房间:

select RPOM_NUMBER
from RESERVATIONS r
group by RPOM_NUMBER
having not exists (
    select *
    from RESERVATIONS 
    where
       RPOM_NUMBER = r.RPOM_NUMBER and
       {start_time} <= DATE_DEPARTED and
       {end_time} >= DATE_ARRIVAL
)