只检查一个表的可用时间？

Question

我有一张桌子，它有一张预订单。每个预订都有一个ID (主键)、一个roomNumber、一个startTime和一个endTime。

我想找出在一定时间范围内有哪些房间可用。所以如果我想知道5点到7点之间是否有免费房间，我会搜索桌子，找出当时没有预订的房间。

一开始我以为我能做到

SELECT DISTINCT roomNumber
FROM  `reservations` A
WHERE NOT 
EXISTS (

SELECT NULL 
FROM  `reservations` B
WHERE A.roomNumber = B.roomNumber
AND (
B.starttime >  '5:00:00'
AND B.starttime <  '7:00:00'
)
OR (
B.endtime >  '5:00:00'
AND B.endtime <  '7:00:00'
)
)

但我很快就意识到了为什么这不管用。因为所有的预订都在同一张桌子上，如果它找到一排没有时间冲突的房间号，它就会返回房间号。这意味着，如果4号房间预订了5-7，另一个预订在3-4，我的查询仍然会返回第4号房间，因为3-4行在技术上是根据我的情况计算为True的。

我不能修改表，我也不知道该怎么做，因为我从来没有遇到过这样的事情。

Answer 1

试着把你的条件换成这些。

SELECT DISTINCT roomNumber
FROM  `reservations` A
WHERE NOT 
EXISTS (

SELECT NULL 
FROM  `reservations` B
WHERE A.court = B.court
AND (
B.starttime <  '7:00:00'
AND B.endtime >  '5:00:00'
)
)

Answer 2

简化查询：

SELECT DISTINCT roomNumber FROM reservations 
WHERE 
    (startTime NOT BETWEEN '05:00:00' AND '07:00:00')
AND (endTime   NOT BETWEEN '05:00:00' AND '07:00:00')

演示

Answer 3

我认为这是可行的：

WITH ALL_ROOMS as (SELECT DISTINCT roomNumber
FROM  `reservations`),

BOOKED_ROOMS as (
SELECT DISTINCT A.roomNumber
FROM ALL_ROOMS A
LEFT JOIN
(SELECT roomNumber, starttime
FROM 'reservations') B
LEFT JOIN
(SELECT roomNumber, endtime
FROM 'reservations') C
)
WHERE (B.starttime > '5:00:00' AND B.starttime <  '7:00:00')
OR (B.endtime > '5:00:00' AND B.endtime <  '7:00:00')
)

SELECT A.roomNumber
from ALL_ROOMS A
LEFT JOIN BOOKED_ROOMS B
ON A.roomNumber = B.roomNumber
WHERE B.roomNumber is NULL