我正设法增加一个小时的客房供应量。例如,如果房间是从上午9点到10点,从上午12点到下午3点,我试图得到从可用时间到可用时间的1小时增量
我可以留在桌上,得到空余的房间,但不是时间。
以下是我的相关模式:
酒店:
Id | name
预订:
Id | hotel_id | room_id | start | end | status
房间:
Id | hotel_id | name | number | available_from | available_to
以下是我到目前为止的疑问:
SELECT r.id, r.name, r.number, r.type, r.rating
FROM rooms r
LEFT OUTER JOIN reservations res ON res.room_id = r.id
AND CURRENT_TIMESTAMP BETWEEN r.available_from AND r.available_to
GROUP BY r.id, r.type
例子:
(这是我试图从数据库中获取的数组。忽略属性名称):
[{“roomnumber”:1,“可用时间”:[“2019-01-01 00:00:00”,“2019-01-01 01 01:00:00”,“2019-01-01 02:00:00”,“2019-01-01 03:00:00”,“2019-01-01 04:00:00”,“2019-01-01 05:00:00”,“2019-01-01 06:00:00”,“2019-01-01 07:00:00”,“2019-01-01 08:00:00”,“2019-01-01 09:00:00”,“2019-01-01 10:00:00”,“2019-01-01 11:00”,“2019-01-01 12:00:00”,“2019-01-01 13:00:00”,"2019-01-01 14:00:00","2019-01-01 15:00:00","2019-01-01 16:00:00","2019-01-01 17:00:00","2019-01-01 18:00:00","2019-01-01 19:00:00","2019-01-01 20:00:00","2019-01-01 21:00:00","2019-01-01 22:00:00","2019-01-01 23:00:00"]}]
我尝试了以下方法:
SELECT free_from, free_until
FROM (
SELECT a.end AS free_from,
(SELECT MIN(c.start)
FROM reservations c
WHERE c.start > a.end) as free_until
FROM reservations a
WHERE NOT EXISTS (
SELECT 1
FROM reservations b
WHERE b.start BETWEEN a.end AND a.end + INTERVAL 1 HOUR
)
AND a.end BETWEEN '2019-01-03 09:00' AND '2019-01-03 21:00'
) as d
ORDER BY free_until-free_from
LIMIT 0,3;
但我得到一行只返回一个结果,这是不正确的。我怎样才能解决这个问题?
样本数据:
酒店:
1 | Marriott
预订:
1 | 1 | 1 | 2019-01-03 15:00:00 | 2019-01-03 17:00:00 | Confirmed
1 | 1 | 1 | 2019-01-03 18:00:00 | 2019-01-03 20:00:00 | Confirmed
房间:
1 | 1 | "Single" | 528 | 09:00:00 | 21:00:00
预期结果
房间id |房间名称|可用时间
1 | "Single" | 2019-01-03 09:00:00, 2019-01-03 10:00:00, 2019-01-03 11:00:00, 2019-01-03 12:00:00, 2019-01-03 13:00:00, 2019-01-03 14:00:00, 2019-01-03 17:00:00, 2019-01-03 20:00:00, 2019-01-03 21:00:00, 2019-01-03 22:00:00, 2019-01-03 23:00:00, 2019-01-03 24:00:00
4条答案
按热度按时间mmvthczy1#
如果将times\u slots表添加到数据库中,如以下sql文件所示:
然后,以下查询将提供所有已预订房间的可用性:
查询1:
此查询首先从中选择可用的插槽
Times_Slots
每天至少有一个房间预订,然后过滤出预订的时间段。结果:
在示例输出中,您指出该房间的可用时间为2019-01-03 22:00:00、2019-01-03 23:00:00、2019-01-03 24:00:00,但是这些时间在房间表定义的可用性块之后,因此我的查询排除了这些时间。
ejk8hzay2#
方案a(每小时一排)
去掉t和z;mysql不理解这种语法。
你的汽车旅馆在同一个时区,对吗?然后使用
DATETIME
或者TIMESTAMP
是等价的。预订3小时,请排3排(使用靶场可能会更麻烦。)
唉,您使用的是mysql,而不是mariadb;后者有自动序列发生器。示例:名为
seq_0_to_23
就像一个预先填充了数字0到23的表。查找可用时间需要有一个表,其中包含所有天中所有可能的小时数,因此上面有注解。
要么做算术,要么留几个小时:
因为左撇子简单明了,
我来讨论一下:
第二列显示一个字符串,可用于指示当天下午1点的时间。
方案b(范围)
另一种方法使用范围。然而,由于所有时间总是与预订或“可用”相关联,因此处理是复杂的。代码变得复杂,但性能很好:http://mysql.rjweb.org/doc.php/ipranges
方案c(bits)
这个表包含一个日期(没有时间),加上一个
MEDIUMINT UNSIGNED
正好是24位。每一位代表一天中的一小时。使用各种布尔运算:
|
(或)将这些位放在一起以查看分配的小时数。0xFFFFFF & ~hours
查看可用的内容。BIT_COUNT()
数位(小时)。虽然在sql中可以确定房间的可用时间,但在客户机代码中这样做可能更好。我假设你有一个php/java/任何前端!
等。
更多?
你想更详细地讨论一下吗?
xmq68pz93#
您需要将rooms表与时间段表(24行)连接起来。这将生成给定房间的所有可能时隙的列表。过滤掉不可用的时隙非常简单:
db小提琴演示
以上查询检查一个日期(2019-01-03)的可用性。对于多个日期,只需将它们添加到
checkdates
.0pizxfdo4#
您遇到的第一个问题是模式设置很差。你没有很好的数据规范化。1) 重命名字段以提高清晰度。2) 将这两个表更改为:
您需要一个定义了时间段的表。你可以用cte然后
CROSS JOIN
把它和你的房间放在一起。这是为数不多的CROSS JOIN
是有用的。现在执行以下查询:
这将为您提供一份所有酒店房间的列表,每个房间有24条记录。如果在那个房间有预订,那么它会向您显示该插槽的预订id。在这里,您可以按原样使用数据,也可以进一步将其放入自己的cte中,只需从中选择保留id为null的所有内容。您还可以根据该id加入或查找有关预订的其他数据。
更新
如果运行mysql 8.0之前的版本,则
WITH
不支持子句(请参阅:mysql中如何使用“with”子句?)。您必须使其成为如下子查询: