检查只有一张桌子的可用时间? [英] Checking for available times with only 1 table?

问题描述

我有一张桌子，上面有预订清单.每个预订都有一个 ID(主键)、roomNumber、startTime 和 endTime.

I have a single table and it has a list of reservations. Each reservations has a ID (primary key) a roomNumber, a startTime and an endTime.

我正在尝试查找在特定时间范围内哪些房间可用.因此，如果我想查看 5 到 7 点之间是否有免费房间，我会搜索表格并找到当时没有预订的房间.

I'm trying to find which rooms are available in a certain time frame. So if I wanted to see if there were any rooms free between 5-7 I'd search the table and find any rooms that don't have reservations at that time.

起初我以为我可以这样做:

At first I thought I could do this:

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.

But I quickly realized why this didn't work. Since all the reservations are in the same table it will return the room number if it ever find a row where the times do not conflict. Meaning if room 4 had a reservation at 5-7 and another one at 3-4 my query would still return room 4 because the 3-4 row does technically evaluate to True based on my condition.

我无法修改表格，我不知道该怎么做，因为我从未遇到过这样的事情.

I can't modify the table and I'm not sure what to do because I've never encountered something like this.

推荐答案

尝试将您的条件更改为这些.

Try changing your conditions to these.

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'
)
)

这篇关于检查只有一张桌子的可用时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！