mysql判断时间重叠,MySQL - 查找时间重叠

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

Booking

=======

booking_id

booking_start

booking_end

resource_booked

===============

booking_id

resource_id

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.

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:

Joining the 2 tables (Booking, Booked_resource) into one table with the 4 attributes needed.

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

I would really appreciate any help on this! Thanks!

EDIT:

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

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).

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!

解决方案

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

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

This is how it was tested:

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)