sql-为预订系统查找免费插槽

ubby3x7f 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(348)

我有一张简单的mysqltable，可以放已经订好的房间。下面是sql结构：

CREATE TABLE Rooms (
   `ID` int, `Description` varchar(50)
);

INSERT INTO Rooms values
(123, 'Room in Frankfurt'),
(234, 'Room in Wiesbaden'),
(245, 'Room in Darmstadt');

CREATE TABLE Bookings (
   `ID` int, `StartTime` datetime, `EndTime` datetime, `post_ID` int
);

INSERT INTO Bookings
    (`ID`, `StartTime`, `EndTime`, `post_ID`)
VALUES
    (1, '2018-01-05', '2018-04-05', 123),
    (2, '2018-08-01', '2018-10-01', 123),
    (3, '2019-02-01', '2019-06-01', 123),
    (4, '2018-02-01', '2018-06-01', 234),
    (5, '2018-08-01', '2018-09-01', 294),
    (6, '2018-09-01', '2018-11-30', 234),
    (7, '2018-11-01', '2018-12-30', 294)
;

在这张表中，我们可以看到所有房间的所有预订情况。我的问题是找到一个sql查询来查找房间的空闲插槽。用户可以给出以下参数：
最早入住日期（例如：2018-10-01）
最新入住日期（例如：2018-10-15）
以月为单位的最长期限（例如：3个月）
所以用户需要一个房间3个月，从01-15开始。2018年10月。
我该怎么做？我真的不明白。
谢谢！

mysql free slots timeslots

来源：https://stackoverflow.com/questions/52740231/sql-find-free-slots-for-booking-system

3条答案

按热度按时间

8wtpewkr1#

Supplied criteria:

     1.  a date for the earliest check-in (ex: 2018-10-01)
     2.  a date for the latest check-in (ex: 2018-10-15)
     3.  a maximum period in months (example: 3 months)

这里有一个只考虑标准1和3的解决方案。。。

SELECT r.*
  FROM rooms r
  LEFT 
  JOIN bookings b
    ON b.post_id = r.id
   AND b.starttime < '2018-10-01' + INTERVAL 3 MONTH
   AND b.endtime > '2018-10-01'
 WHERE b.id IS NULL;
+------+-------------------+
| ID   | Description       |
+------+-------------------+
|  123 | Room in Frankfurt |
|  245 | Room in Darmstadt |
+------+-------------------+

但你真正的问题似乎是：
从10月1日开始的45天内，哪些房间有“3个月的可用性”。这是一个稍有不同（我认为更复杂）的问题。

赞(0）回复(0）举报 2021-06-18

dgsult0t2#

首先，让我们看看每个房间的空间隔：

select room_ID, a.EndTime, b.StartTime
from Bookings a
join (select c.StartTime
      from Bookings c
      where c.room_ID = a.room_ID and 
      not exists (select 1
                  from Bookings d
                  where d.room_ID = c.room_ID and d.StartTime > a.EndTime and d.StartTime < c.StartTime)) b;

这应该是 union 在这段时间里 now() 以及 StartTime 第一个元素。这样你就会有一个结果，从中你可以得到你问题的答案。我不打算为你的作业实施解决方案，这个答案只是一个解决方案的起点。

赞(0）回复(0）举报 2021-06-18

4sup72z83#

我想你有某种 Rooms table
http://sqlfiddle.com/#!977年2月9日

SELECT r.*
FROM rooms r
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= '2018-10-01'
   AND b.EndTime >= '2018-10-01')
     OR
     (b.StartTime >= '2018-10-01'
   AND b.StartTime <= '2018-10-15')
   )     
WHERE b.id IS NULL

更新我还是不确定我是否有你的目标。但这里有另一种方法。如果你想开始日期是灵活的，我建议设置 calendar 一年中所有日期的表。这将允许在运行查询时避免不必要的计算。
这是一把小提琴：http://sqlfiddle.com/#!9/29926/3

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND (
     (b.StartTime <= c.date
   AND b.EndTime >= c.date)
     OR
     (b.StartTime >= c.date
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH))
   )     
WHERE b.id IS NULL

更新2结合@strawberry answer我想我们可以将查询修改为：
http://sqlfiddle.com/#!9/29926/5

SELECT r.*,
c.date
FROM rooms r
LEFT JOIN calendar c
ON c.date BETWEEN '2018-10-01' AND '2018-10-15'
LEFT JOIN Bookings b
ON r.id = b.post_id
   AND b.StartTime <= (c.date + INTERVAL 3 MONTH)
   AND b.EndTime >= c.date     
WHERE b.id IS NULL

赞(0）回复(0）举报 2021-06-18

我来回答

sql-为预订系统查找免费插槽

3条答案

相关问题

热门标签

最新问答