用MySQL检测资源调度冲突

Question

如何查询以下三个表以检测资源调度冲突？当两个日期/时间范围重叠的events需要共同的participants时，就会发生冲突。

CREATE TABLE `events` (
  id int(11) AUTO_INCREMENT,
  `date` date,               # date on which the event occurs
  start_minute smallint(6),  # minute of day on which event starts
  end_minute smallint(6),    # minute of day on which event ends
);

CREATE TABLE participants (
  id int(11) AUTO_INCREMENT,
  `name` varchar(255),
);

CREATE TABLE event_participations (
  event int(11),             # refers to events.id
  participant int(11),       # refers to participants.id
  PRIMARY KEY (event,participant)
);

我认为以下查询将检测到重叠事件：

SELECT *
FROM events e CROSS JOIN events e2
WHERE e.date=e2.date AND e.start_minute<e2.end_minute AND e2.start_minute<e.end_minute

但是，重叠并不意味着冲突，因为两个events可能涉及相互排斥的participants集。如何查询同时需要相同参与者的事件对？

Answer 1

要么像这样：

SELECT DISTINCT 
'this events and participations overlap',
e.*
FROM events e 
JOIN event_participations ep ON e.id = ep.event
WHERE EXISTS (SELECT 1 
              FROM events e2
              JOIN event_participations ep2 ON e2.id = ep2.event 
              WHERE e.date = e2.date 
                AND e2.start_minute < e.end_minute 
                AND e2.end_minute > e.start_minute
                AND ep2.participant = ep.participant
             )

或者像这样：

SELECT DISTINCT 
'this events and participations overlap',
e.*
FROM events e 
JOIN event_participations ep ON e.id = ep.event
JOIN events e2 ON  e.date = e2.date 
               AND e2.start_minute < e.end_minute 
               AND e2.end_minute > e.start_minute
JOIN event_participations ep2 ON e2.id = ep2.event 
WHERE ep2.participant = ep.participant

关于EXISTS的好处是，它一碰到就停止搜索。JOIN方法构建整个数据集。