我有一个包含事件的数据库表:

CREATE TABLE events
event VARCHAR(32)
,down_time TIMESTAMP
,up_time TIMESTAMP
,id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY
,INDEX(event(16))
);

INSERT INTO events(event, down_time, up_time) VALUES
('e1', '2015-01-01 00:00:04', '2015-01-01 00:00:08'),
('e2', '2015-01-01 00:00:05', '2015-01-01 00:00:06'),
('e3', '2015-01-01 00:00:02', '2015-01-01 00:00:09'),
('e4', '2015-01-01 00:00:01', '2015-01-01 00:00:07'),
('e5', '2015-01-01 00:00:03', '2015-01-01 00:00:10');

SELECT * FROM events;

+-------+---------------------+---------------------+----+
| event | down_time           | up_time             | id |
+-------+---------------------+---------------------+----+
| e1    | 2015-01-01 00:00:04 | 2015-01-01 00:00:08 |  1 |
| e2    | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 |  2 |
| e3    | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 |  3 |
| e4    | 2015-01-01 00:00:01 | 2015-01-01 00:00:07 |  4 |
| e5    | 2015-01-01 00:00:03 | 2015-01-01 00:00:10 |  5 |
+-------+---------------------+---------------------+----+
5 rows in set (0.00 sec)

    1   2   3   4   5   6   7   8   9   10
    |   |   |   |   |   |   |   |   |   |
    |   |   |   |-------e1------|   |   |
    |   |   |   |   |-e2|   |   |   |   |
    |   |--------------e3-----------|   |
    |----------e4-----------|   |   |   |
    |   |   |-------------e5------------|

我想确定所有事件是否在任一时间重叠。 所以在上面它们都在这里相交(e2):

Overlap |overlap_down_time   | overlap_up_time     | overlap_duration
1       |2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01

我可以使用此查询找到事件对之间的重叠，但不确定如何包含所有事件..非常感谢!

SELECT
  e1.event AS event1_name,
  e1.down_time AS event1_down_time,
  e1.up_time AS event1_up_time,
  TIMEDIFF(e1.up_time, e1.down_time) AS event1_duration,

  e2.event AS event2_name,
  e2.down_time AS event2_down_time,
  e2.up_time AS event2_up_time,
  TIMEDIFF(e2.up_time, e2.down_time) AS event1_duration,

  GREATEST(e1.down_time,e2.down_time) AS overlap_down_time, 
  LEAST(e1.up_time,e2.up_time) AS overlap_up_time,
  TIMEDIFF( LEAST(e1.up_time,e2.up_time),
  GREATEST(e1.down_time,e2.down_time) ) AS overlap_duration

 FROM events e1

INNER JOIN events e2 ON e1.id < e2.id
WHERE
 ( e2.down_time <= e1.up_time ) 
AND
 ( e2.up_time >= e1.down_time );

下面提出的解决方案似乎没有涵盖事件在整个时间范围内发生两次的情况......如下面的 e2 所发生的那样:

1   2   3   4   5   6   7   8   9   10
|   |   |   |-------e1------|   |   |
|   |   |---e2--|   |-------e2------|   
|   |-------------e3------------|   |
|----------e4-----------|   |   |   |

INSERT INTO events(event, down_time, up_time) VALUES
    ('e1', '2015-01-01 00:00:04', '2015-01-01 00:00:08'),
    ('e2', '2015-01-01 00:00:03', '2015-01-01 00:00:05'),
    ('e2', '2015-01-01 00:00:06', '2015-01-01 00:00:10'),
    ('e3', '2015-01-01 00:00:02', '2015-01-01 00:00:09'),
    ('e4', '2015-01-01 00:00:01', '2015-01-01 00:00:07');

我已经能够通过使用两阶段查询来识别相交:

CREATE VIEW overlap1 AS
SELECT
CONCAT(a.event,'-', b.event) overlaps,
GREATEST(a.down_time,b.down_time) AS downtime, 
LEAST(a.up_time,b.up_time) AS uptime,
TIME_TO_SEC(TIMEDIFF( LEAST(a.up_time,b.up_time),
    GREATEST(a.down_time,b.down_time))) AS duration

FROM events a 
JOIN events b 
ON  a.event < b.event 
    AND (a.event = 'e1' OR a.event = 'e2' OR a.event = 'e3' OR a.event = 'e4')
    AND (b.event = 'e1' OR b.event = 'e2' OR b.event = 'e3' OR b.event = 'e4') 
WHERE   ( a.down_time <= b.up_time )
AND     ( a.up_time >= b.down_time );

SELECT * FROM overlap1;
+----------+---------------------+---------------------+----------+
| overlaps | downtime            | uptime              | duration |
+----------+---------------------+---------------------+----------+
| e1-e2    | 2015-01-01 00:00:04 | 2015-01-01 00:00:05 |        1 |
| e1-e2    | 2015-01-01 00:00:06 | 2015-01-01 00:00:08 |        2 |
| e1-e3    | 2015-01-01 00:00:04 | 2015-01-01 00:00:08 |        4 |
| e2-e3    | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 |        2 |
| e2-e3    | 2015-01-01 00:00:06 | 2015-01-01 00:00:09 |        3 |
| e1-e4    | 2015-01-01 00:00:04 | 2015-01-01 00:00:07 |        3 |
| e2-e4    | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 |        2 |
| e2-e4    | 2015-01-01 00:00:06 | 2015-01-01 00:00:07 |        1 |
| e3-e4    | 2015-01-01 00:00:02 | 2015-01-01 00:00:07 |        5 |
+----------+---------------------+---------------------+----------+

CREATE VIEW overlap2 AS
SELECT
CONCAT(a.overlaps,'-',b.overlaps) AS overlaps,
GREATEST(a.downtime,b.downtime) AS downtime,
LEAST(a.uptime,b.uptime) AS uptime,
TIMEDIFF( LEAST(a.uptime,b.uptime),
GREATEST(a.downtime,b.downtime) ) AS duration

FROM overlap1 a
JOIN overlap1 b 
ON a.overlaps < b.overlaps
AND (a.overlaps = 'e1-e2' OR a.overlaps = 'e3-e4')
AND (b.overlaps = 'e1-e2' OR b.overlaps = 'e3-e4')

WHERE   ( a.downtime <= b.uptime )
AND ( a.uptime >= b.downtime );

SELECT * FROM overlap2;
+-------------+---------------------+---------------------+----------+
| overlaps    | downtime            | uptime              | duration |
+-------------+---------------------+---------------------+----------+
| e1-e2-e3-e4 | 2015-01-01 00:00:04 | 2015-01-01 00:00:05 | 00:00:01 |
| e1-e2-e3-e4 | 2015-01-01 00:00:06 | 2015-01-01 00:00:07 | 00:00:01 |
+-------------+---------------------+---------------------+----------+

如果可能的话，我想通过一个查询来实现这一点..感谢任何帮助!

最佳答案

以下查询将满足您的要求:

select e1.* from events e1
join events e2 on e1.down_time>=e2.down_time and e1.up_time<=e2.up_time
group by e1.event
having count(*)=(select count(*) from events)

在 http://sqlfiddle.com/#!9/7c733/4 验证结果

关于MySQL找到重叠的交集，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32789962/