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

Question

我仔细地阅读了关于这个话题的所有帖子，但我的问题还有一个额外的问题，我需要每个房间都有可用的床位，因为这是一家招待所，有些房间是共用的。

现在我已经尝试在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       ||
----------------------------

你可以在图片中看到这一点，我在我的应用程序中展示了它的外观。两次约会之间唯一可用的房间和床是豪华房及其第一床和第二床，以及宿舍床5、6、7、8，因为1-4至少预定了一个预定日期。

我唯一的想法是使用NOT，但只有当我不关心床，而且输出也是这里的时候，才能起作用。

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

​

​

而不是我上面的“素描”

我想知道如何处理这个问题的任何建议和想法。我的一部分担心，这一切都会归结到我不把床当作实体和必须这样做，即使我将永远不会存储任何类型的信息在床上的颜色，位置，质量，价格等.

回复@Paul

这是令人惊讶的，但有什么方法可以得到免费的床以及数字，而不是总数。因为当那个人预订的时候，我不得不把它分配到床上。所以如果结果是

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

而不是

​

​

Answer 1

您可以通过以下查询获得多个免费房间(以及预订的房间号)：

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 (左连接) 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

要列出所有非resverd床位，可以将其与预订床的查询结合起来(选择在预订日期范围内未保留的所有床位)：

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