php - 从数据库中检索给定日期范围内的可用房间及其床铺

我已经仔细阅读了所有关于这个话题的帖子，但是我的问题还有一个额外的问题，我需要在每个房间都有可用的床位，因为它是一个旅社，有些房间是共享的。
现在我已经尝试在PHP中执行此操作，但后来意识到我甚至没有考虑日期范围。所以现在我在想，因为我已经有了一个查询，它检索在给定日期范围内发生的所有预订，然后将检索到的房间及其床位与房间表进行比较，只显示未占用的房间及其床位。但我不知道如何在床边工作，因为它们不是一个实体，只是每个房间的总床位数。但是，然后一个预订告诉我们预订房间里的哪张床。。
这是我的桌子
房间

保留

现在，我用来检索所有预订房间及其床位的查询是

SELECT rooms_id, bed
FROM reservations
WHERE `to` > '2016-02-18' AND `from` < '2016-02-24'

如果输入变量$from，则第一个日期；如果第二个日期是输入变量$to，则它不仅允许检索从$from和$to开始的预订房间，还允许检索在日期范围之前开始并在日期范围内结束的所有预订，在日期范围内开始并在日期范围之后结束，最后在日期范围之前开始并在日期范围之后结束的预订。所以上面的查询将返回下表

然后我就可以在我的应用程序中这样可视化

但这就是我陷入困境的地方。我不知道如何匹配我的数据，以便找到所有可用的房间，但也床。
日期范围从“2016-02-18”到“2016-02-24”所需的可用客房应如下所示：

|||||||||||||||||||||||||||||||||
|| rooms_id || bed_number ||
||||||||||||||||||||||||||||
||    1     ||    1       ||
----------------------------
||    1     ||    2       ||
----------------------------
||    2     ||    5       ||
----------------------------
||    2     ||    6       ||
----------------------------
||    2     ||    7       ||
----------------------------
||    2     ||    8       ||
----------------------------

你可以在图片中看到，我在我的应用程序中展示了它的外观。两个日期之间唯一可用的房间和床是豪华房及其1号床和2号床，以及宿舍5、6、7、8号床，因为1-4号床至少在一个预期日期预订
我唯一的想法是不用在里面，但如果我不在乎床的话，那就行了，而且产量也在这里

SELECT *
FROM `rooms`
WHERE `id` NOT IN
     (SELECT rooms_id FROM reservations WHERE `to` > '2016-02-18' AND `from` < '2016-02-24')

而不是我在上面画的
我很感激你的建议和想法。
我有一部分担心，这一切都会落到我的头上，我不把床当作实体来对待，必须这样做，即使我永远不会在床上存储任何类型的信息，如它们的颜色、位置、质量、价格等。。。
回复@Paul Spiegel
这是惊人的，但有没有办法得到免费的床作为数字，而不是总数。因为当这个人预订的时候，我必须把它分配到床上。所以如果结果是

| room_id | title       | beds_total | available_bed_nrs |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1       | luxury room | 2          | 1, 2              |
| 2       | dorm room   | 8          | 5, 6, 7, 8        |

而不是

最佳答案

通过此查询，您可以获得许多免费房间（以及预订的房间号）：

set @from := '2016-02-18';
set @to   := '2016-02-24';
set @beds := 1;

SELECT rm.id, rm.title, rm.beds,
    rm.beds - IFNULL(rv.num_reserved_beds, 0) AS num_free_beds,
    rv.reserved_bed_nrs
FROM rooms rm
LEFT JOIN (
    SELECT rv.rooms_id,
        COUNT(1) as num_reserved_beds,
        GROUP_CONCAT(rv.bed) as reserved_bed_nrs
    FROM reservations rv
    WHERE rv.from < @to
      AND rv.to   > @from
    GROUP BY rv.rooms_id
) rv ON rv.rooms_id = rm.id
HAVING num_free_beds >= @beds

现在您可以解析reserved_bed_nrs，在每个房间的所有床上循环，并选择不在reserved_bed_nrs中的床。
说明：
在日期范围内预订所有床位（不包括）：

SELECT *
FROM reservations r
WHERE r.from < @to
  AND r.to   > @from;

按房间分组，计算预订房间的数量，并将所有预订房间的数量存储在一个字符串字段中：

SELECT rv.rooms_id,
    COUNT(1) as num_reserved_beds,
    GROUP_CONCAT(rv.bed) as reserved_bed_nrs
FROM reservations rv
WHERE rv.from < @to
  AND rv.to   > @from
GROUP BY rv.rooms_id

Join（LEFT Join）rooms根据给定的结果计算免费床位的数量，并将其与您要预订的床位数量进行比较。
更新如何获得免费（未预订）床：
如果你没有一张有所有现有床位的桌子，你就需要一些排序号码。假设一个房间最多可以有100张床，你可以用100个数字创建一个sequence表：

CREATE TABLE `sequence` (
    `nr` TINYINT(3) UNSIGNED NOT NULL,
    PRIMARY KEY (`nr`)
) select d1.d*10 + d0.d + 1 as nr   from
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d0,
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d1

现在有可能通过交叉连接表rooms和sequence列出所有现有的床：

SELECT *
FROM rooms rm
CROSS JOIN sequence seq
WHERE seq.nr <= rm.beds

要列出所有未预订的床位，您可以将其与预订床位查询相结合（选择预订日期范围内未预订的所有床位）：

SELECT *
FROM rooms rm
CROSS JOIN sequence seq
WHERE seq.nr <= rm.beds
  AND (rm.id, seq.nr) NOT IN (
        SELECT rv.rooms_id, rv.bed
        FROM reservations rv
        WHERE rv.from < '2016-02-24'
          AND rv.to   > '2016-02-18'
  )

这也可以通过NOT EXISTS或排除LEFT JOIN来实现。
您还可以跳过sequence表的创建使用创建代码作为子选择：

SELECT *
FROM rooms rm
CROSS JOIN (
    select d1.d*10 + d0.d + 1 as nr
    from
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d0,
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d1
) seq
WHERE seq.nr <= rm.beds
  AND (rm.id, seq.nr) NOT IN (
        SELECT rv.rooms_id, rv.bed
        FROM reservations rv
        WHERE rv.from < '2016-02-24'
          AND rv.to   > '2016-02-18'
  )

http://sqlfiddle.com/#!9/a0d61/5