mysql - mysql查询以获取日期范围未给出预期结果之间所有未预订的房间

我正在创建一个预订系统，它有两张桌子
房间

id  | name          | no_of_rooms
1   | Lake View     | 4
2   | Royale        | 2
3   | Sky View      | 6

预订

id |room_id     | start             | end           | total_rooms
4  |1           | 2015-08-13        | 2015-08-14    | 2
5  |2           | 2015-08-20        | 2015-08-22    | 1
6  |2           | 2015-08-01        | 2015-08-13    | 1

在搜索页面上创建新预订时，我希望找到在此日期范围内可用的房间
搜索值
开始时间：2015-08-11
结束日期：2015-08-12
预期结果

id | name       | no_of_rooms       | available_rooms
1  | Lake View  | 4                 | 4
2  | Royale     | 2                 | 1
3  | Sky View   | 6                 | 6

我得到的结果

id  | name          | no_of_rooms   | available_rooms
1   | Lake View     | 4             | 0
2   | Royale        | 2             | 1
3   | Skyview       | 6             | 0

这是我写的房间可用性代码

SELECT
    r.id,
    r.name,
    r.no_of_rooms,
    IFNULL(sum(b.total_rooms),0) as available_rooms
FROM rooms r
LEFT JOIN bookings b ON r.id = b.room_id AND
(
    (b.start <= "2015-08-11" AND b.end >= "2015-08-11")
    OR
    (b.start <= "2015-08-12" AND b.end >= "2015-08-12")
)
GROUP BY r.id

我也不想让显示不可用的房间。无论我尝试了什么结果都是完全错误的。希望你们能帮助我！

最佳答案

您可以尝试：

SELECT r.id AS roomID, r.name, r.no_of_rooms,
       r.no_of_rooms - COALESCE(t.bookedRooms,0) AS available_rooms
FROM rooms AS r
LEFT JOIN (
   SELECT room_id, SUM(total_rooms) AS bookedRooms
   FROM bookings
   WHERE `start` < '2015-08-14' AND `end` > '2015-08-11'
   GROUP BY room_id ) AS t
ON r.id = t.room_id
WHERE r.no_of_rooms - COALESCE(t.bookedRooms,0) > 0

查找指定期间的预订房间的关键是以下谓词：

`start` <= '2015-08-12' AND `end` >= '2015-08-11'

以上是预订周期与指定周期重叠的所有房间，即使是一天。
使用GROUP BYonroom_id可以得到指定期间每个room_id的预订房间总数。
为了得到预期的结果集，我们可以用预订总量的派生表做一个LEFT JOIN，然后简单地从可用房间的数量中减去预订房间的总和。
Demo here

关于mysql - mysql查询以获取日期范围未给出预期结果之间所有未预订的房间，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32142460/