MySQL - 查找时间重叠 [英] MySQL - Finding time overlaps

问题描述

我在数据库中有两个具有以下属性的表：

I have 2 tables in the database with the following attributes:

Booking
=======
booking_id
booking_start
booking_end

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

第二个表是预订和资源之间的关联实体，1次预订可以包含许多资源）。属性booking_start和booking_end是其中包含日期和时间的时间戳。

The second table is an associative entity between "Booking" and "Resource" (i.e., 1 booking can contain many resources). Attributes booking_start and booking_end are timestamps with date and time in it.

我可以知道如何查找每个resource_id（resource_booked）如果日期/时间与类似resource_id的其他预订重叠或冲突？

May I know how I might be able to find out for each resource_id (resource_booked) if the date/time overlaps or clashes with other bookings of similar resource_id?

我在纸上涂鸦的答案，以图片方式看看它是否可以帮助我可视化我如何解决这个问题，得到：

I was doodling the answer on paper, pictorially, to see if it might help me visualize how I could solve this and I got this:

1. 将2个表格（Booking，Booked_resource）加入需要4个属性的表格中。


2. 按照此处建议的答案：查找重叠（日期/时间）行

1. Joining the 2 tables (Booking, Booked_resource) into one table with the 4 attributes needed.
2. Follow the answer suggested here : Find overlapping (date/time) rows within one table

我做了第一步，但步骤2让我很难过！

I did step 1 but step 2 is leaving me baffled!

我真的很感激任何帮助！谢谢！

I would really appreciate any help on this! Thanks!

编辑：
我正在阅读Renshaw先生的回答，并尝试自己做一个查看我是否明白，我得到的概念：

I was reading Mr Renshaw's answer and tried doing one on my own to see if I understood and I got the concept:

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但不同的booking_id，并查看一个（booking_id）的booking_start datetime是否在另一个（booking_id）的booking_start和booking_end之间。

I think I was trying to create 2 identical tables and join them up with resource_id, find records with similar resource id but different booking_id and see if the booking_start datetime of one (booking_id) is between the booking_start and booking_end of another (booking_id).

这真是混乱，确保如果我的查询是询问我的想法，但是有一些奇迹，我得到了与Renshaw一样的答案。

It's really messy and I wasn't even sure if my query was asking what I had in mind but by some miracle, I got the same answer as Mr Renshaw!

推荐答案

编辑：使用额外的信息，现在只限于显示那些与一些EARLIER预订冲突的预订。

using additional info, this is now limited to showing only those bookings that clash with some EARLIER booking.

我认为这个wll做的伎俩：

I think this wll do the trick:

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)

这篇关于MySQL - 查找时间重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！