时间戳

首页 > 解决方案 > SQL 选择重叠行

mysql - SQL 选择重叠行

问题描述

我得到了这个表操作：

+-----------+---------------------+----------+
| theatreNo |    startDateTime    | duration |
+-----------+---------------------+----------+
|         1 | 2019-05-12 09:30:00 | 04:50:00 |
|         3 | 2019-05-17 15:05:00 | 02:00:00 |
|         3 | 2019-05-17 16:05:00 | 03:00:00 |
+-----------+---------------------+----------+

而且我正在尝试查找我们是否有两个操作重叠的剧院，它应该输出以下内容：

+--------+---------------+----------+----------+
| roomNo | startDateTime | firstOp  | secondOp |
+--------+---------------+----------+----------+
|      3 | 2019-05-17    | 15:05:00 | 16:05:00 |
+--------+---------------+----------+----------+

这是重叠的，因为一个手术从 15:05 开始，持续时间为 2 小时，而同一剧院的另一个手术从 16:05 开始，但第一个手术尚未完成。

我不知道是否有某种方法可以计算开始时间和持续时间之间的差异，并将其与下一个操作的开始时间进行比较。

标签： mysqlsqldatabaseoverlap

解决方案

You can use join to get pairs of overlaps in the same row:

select o.*, o2.*
from operation o join
     operation o2
     on o2.theatreNo = o.theatreNo and
        o2.startDateTime < o.startDateTime + o.duration and
        o2.startDateTime + o2.duration > o.startDateTime and
        (o2.startDateTime <> o.startDateTime or
         o2.duration <> o.duration
        );

The logic gets much harder if you want to summarize the overlaps. The issue is that multiple time periods could overlap. You can get any pair in the same row -- but that pair might overlap with another, so there would be redundancy.

推荐阅读