MySQL - 查找时间重叠

Question

MySQL - 查找时间重叠

6

我在数据库中有两个表，它们的属性如下：

Booking
=======
booking_id
booking_start
booking_end

resource_booked
===============
booking_id
resource_id

第二个表是"Booking"和"Resource"之间的关联实体（即，1个预订可以包含多个资源）。属性booking_start和booking_end是带有日期和时间戳的时间戳。
我想知道如何为每个resource_id（resource_booked）找出日期/时间是否与类似resource_id的其他预订重叠或冲突？
我在纸上画了答案的图示，看看它是否能帮助我想象如何解决这个问题，结果是：

将两个表（Booking、Booked_resource）连接成一个表，具有所需的4个属性。
按照此处建议的方法进行操作：查找重叠（日期/时间）行一张表中

我完成了步骤1，但步骤2使我感到困惑！
我真的很感激任何帮助！谢谢！
编辑：
我正在阅读Renshaw先生的回答，并尝试自己做一个以查看我是否理解了它，结果是：

SELECT 
  a.* 
FROM 
  (SELECT 
    b.creation_date,
    b.booking_id,
    r_b.resource_id,
    b.booking_start,
    b.booking_end 
  FROM Booking b 
  INNER JOIN resource_booked r_b ON b.booking_id = r_b.booking_id) as a,
  (SELECT
    b.booking_id,
    r_b.resource_id,
    b.booking_start,
    b.booking_end 
  FROM Booking b INNER JOIN resource_booked r_b ON b.booking_id = r_b.booking_id) as
WHERE
  a.resource_id = b.resource_id 
AND 
  a.booking_id <> b.booking_id 
AND 
  a.booking_start BETWEEN b.booking_start AND b.booking_end 
AND
  a.creation_date >= b.creation_date

我认为我试图创建2个相同的表，并通过 resource_id 连接它们，查找具有相似资源 ID 但不同预订 ID 的记录，并查看一个预订 ID 的 booking_start datetime 是否在另一个预订 ID 的 booking_start 和 booking_end 之间。

这非常混乱，我甚至不确定我的查询是否符合我的意图，但出乎意料的是，我得到了与 Renshaw 先生相同的答案！

- Cuppy

2个回答

0

哇！我从来没有想过那个……

我也没想到会得到这么详细的帮助，非常感谢你所付出的努力！我正在尝试理解并学习你的查询 :>

预订表还有另一个属性叫做creation_date，它显示了预订是何时进行的，我希望只显示后面进行的预订（在时间上重叠或冲突）。所以应该是：

Booking 1 
=========
creation_date - 2000-01-01 13:00:00
booking_start - 2000-02-24 12:00:00
booking_end   - 2000-02-24 14:00:00

Booking 2 
=========
creation_date - 2000-01-02 15:00:00
booking_start - 2000-02-24 13:00:00
booking_end   - 2000-02-24 15:00:00

预订1和2之间存在时间重叠，由于预订2比预订1晚一天，因此只会显示预订2。

- Cuppy

最好通过编辑原问题添加类似这样的额外信息。讨论可以使用与问题相关联的评论功能进行。 - Daniel Renshaw

我已经编辑了我的答案，以考虑到这个额外的信息。 - Daniel Renshaw

哦，非常抱歉我没有意识到评论和添加新帖子之间的区别。但是你的答案非常好！非常感谢！ - Cuppy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Renshaw · Accepted Answer

编辑：使用额外信息，现在仅限于显示与某些早期预订冲突的预订。

我认为这会起作用：

SELECT DISTINCT
    b2.booking_id, -- The later booking
    b2.booking_start,
    b2.booking_end,
    b2.creation_date,
    rb1.resource_id, -- The resource over which the two bookings clash
    b1.booking_id, -- The earlier booking
    b1.booking_start,
    b1.booking_end
FROM resource_booked rb1
    INNER JOIN booking b1 ON b1.booking_id = rb1.booking_id
    INNER JOIN booking b2 ON b1.booking_id <> b2.booking_id
        AND b2.booking_start BETWEEN b1.booking_start AND b1.booking_end
    INNER JOIN resource_booked rb2 ON rb2.resource_id = rb1.resource_id
        AND rb2.booking_id = b2.booking_id

测试的方式如下：

use tempdb

drop table resource_booked 
drop table Booking

create table Booking
(
    booking_id int,
    booking_start datetime,
    booking_end datetime,
    creation_date datetime
)

create table resource_booked 
(
    booking_id int,
    resource_id int
)

insert Booking values (1, '1 january 2000', '1 march 2000', '1 january 2000')
insert Booking values (2, '1 february 2000', '1 may 2000', '2 january 2000')
insert Booking values (3, '1 april 2000', '1 june 2000', '3 january 2000')
insert Booking values (4, '1 july 2000', '1 august 2000', '4 january 2000')

insert resource_booked values (1, 1)
insert resource_booked values (2, 1)
insert resource_booked values (3, 1)
insert resource_booked values (4, 1)
insert resource_booked values (1, 2)
insert resource_booked values (3, 2)