sql 查询时间_SQL查询时间段重合的记录

问题：查询某一列/对象时间段重合的记录Time Range Overlap

数据结构：表demo_table，字段如下：

字段	类型
ID	...
object_name/对象	varchar
start_time/开始时间	datetime
end_time/结束时间	datetime
Others/其他字段	...

思路：

假设有A时间段（期间为a1-a2），B时间段（期间为b1-b2），潜在条件a1<a2，b1<b2（越小时间越早）。时间段的关系有三种，其中重合包含两种（情况1或2），如下所示：

情况1：A或B相交但不包含，也不相接；

情况2：A含于B，或B含于A，可以完全重合；

不管是情况1还是2，只要保证一个时间段的起始点或终点落在另一个时间段的期间即可，即 b1<=a1<=b2 或 b1<=a2<=b2，但同时排除期间首尾相连的情况，即 b1<b2=a1<a2 或 a1<a2=b1<b2；A与B对换亦应满足。

情况3：A与B相离，或相接。

情况3满足 a2<=b1 或 满足 b2<=a1；非情况3，也可以作为时间段重合的判断，即 a2>b1且 a1<b2；A与B对换亦应满足。

要查询查询时间段重合的记录，必定是一组或多组，即输出结果中应同时包含A或B，单个时间段是无所谓重合与否的。

实现方法：自连接Inner Join

代码：

--方法1：情况2的判定条件
SELECT DISTINCT t1.* FROM demo_table t1
INNER JOIN demo_table t2
ON t1.object_name=t2.object_name  --以指定的列进行自连接
WHERE 
   t1.ID!=t2.ID --去除自身重复的情况
   AND (
	(t1.start_time >= t2.start_time AND t1.start_time <= t2.end_time)
	OR (t1.end_time >= t2.start_time AND t1.end_time <= t2.end_time)   
	OR (t2.start_time > t1.start_time AND t2.start_time < t1.end_time)
	OR (t2.end_time > t1.start_time AND t2.end_time < t1.end_time) 
   )  
   AND NOT (
	t1.start_time = t2.end_time OR t2.start_time = t1.end_time
   )

--方法2：非情况3-直接表述判定条件
SELECT DISTINCT t1.* FROM demo_table t1
INNER JOIN demo_table t2
ON t1.object_name=t2.object_name  
WHERE
  t1.ID!=t2.ID 
  AND NOT (t2.end_time <= t1.start_time)
  AND NOT (t1.end_time <= t2.start_time)  

--方法3：非情况3-间接表述判定条件
SELECT DISTINCT t1.* FROM demo_table t1
INNER JOIN demo_table t2
ON t1.object_name=t2.object_name 
WHERE
   t1.ID!=t2.ID 
   AND t2.end_time > t1.start_time 
   AND t1.end_time > t2.start_time

从代码可以看出，实际上用方法2或3会比方法1简单得多。

测试数据及检验：

--创建示例表
CREATE TABLE demo_table (
   ID INT PRIMARY KEY
   ,object_name VARCHAR(10) NOT NULL
   ,start_time DATETIME NOT NULL
   ,end_time DATETIME NOT NULL
   ,duration_hours VARCHAR(10)
)

--插入数据
INSERT INTO demo_table VALUES(1,'A','2020/12/31 08:00:00','2020/12/31 16:00:00',8)
INSERT INTO demo_table VALUES(2,'A','2020/12/31 09:00:00','2020/12/31 14:00:00',5)
INSERT INTO demo_table VALUES(3,'A','2020/12/31 10:00:00','2020/12/31 15:00:00',5)
INSERT INTO demo_table VALUES(4,'B','2020/12/31 16:00:00','2020/12/31 18:00:00',2)
INSERT INTO demo_table VALUES(5,'B','2020/12/31 19:00:00','2020/12/31 20:00:00',1)
INSERT INTO demo_table VALUES(6,'C','2020/12/31 20:00:00','2020/12/31 20:00:00',0)
INSERT INTO demo_table VALUES(7,'D','2020/12/31 13:00:00','2020/12/31 16:00:00',3)
INSERT INTO demo_table VALUES(8,'D','2020/12/31 13:00:00','2020/12/31 16:00:00',3)
INSERT INTO demo_table VALUES(9,'E','2020/12/31 21:00:00','2020/12/31 22:00:00',1)
INSERT INTO demo_table VALUES(10,'E','2020/12/31 22:00:00','2020/12/31 23:00:00',1)

生成的示例表如下所示：

demo_table

预期得到的结果应如图所示：

Expected Result

• Author: Seldon He
• Updated on: 2021/01/05
• 欢迎评论交流，如有错误，恳请斧正。
• 本文采用BY-NC-SA许可协议，转载请注明出处。