在SQL Server中合并重叠日期

article/2025/10/22 22:57:54

1.合并SQL中的时间重叠的记录方法:A

--创建临时表，里面有重叠的日期declare @t table (Name varchar(100),  starttime  datetime,  endtime datetime);
insert into @t values('A'   , '2017-01-02 00:00' , '2017-03-28 00:10'),('A'   , '2017-05-14 23:50' , '2017-05-29 23:50'),('B'   , '2017-05-18 00:00' , '2017-05-18 04:00'),('B'   , '2017-05-18 02:00' , '2017-05-18 03:00'),('C'   , '2017-01-02 00:00' , '2017-01-17 15:50'),('C'   , '2017-01-14 03:50' , '2017-01-28 15:50');
--定义临时表:开始日期：去掉开始日期在日期范围内的数据
with starttimes as 
( select distinct name, starttime from @t as t1 where not exists (select * from @t as t2 where t2.name = t1.name and t2.starttime < t1.starttime and t2.endtime >= t1.starttime) 
), 
endtimes as ----结束日期：去掉结束日期在日期范围内的数据
( select distinct name, endtime from @t as t1 where not exists (select * from @t as t2 where t2.name = t1.name and t2.endtime > t1.endtime and t2.starttime <= t1.endtime) 
) 
select name, starttime, (select min(endtime) from endtimes as e --结束日期取最小值where e.name = s.name and endtime >= starttime) as endtime 
from starttimes as s;

结果：

变形（过程相同，语句稍微有点不一样）：

declare @t table (Name varchar(100),  starttime  datetime,  endtime datetime);
insert into @t values('A'   , '2017-01-02 00:00' , '2017-03-28 00:10'),('A'   , '2017-05-14 23:50' , '2017-05-29 23:50'),('B'   , '2017-05-18 00:00' , '2017-05-18 04:00'),('B'   , '2017-05-18 02:00' , '2017-05-18 03:00'),('C'   , '2017-01-02 00:00' , '2017-01-17 15:50'),('C'   , '2017-01-14 03:50' , '2017-01-28 15:50');--干掉起始点时间在某记录起止时间之间的
SELECT a.name,a.starttime
FROM @t a LEFT OUTER JOIN @t b
ON a.name=b.name and a.starttime>b.starttime AND a.starttime<=b.endtime GROUP BY a.name,a.starttime HAVING(COUNT(b.starttime)=0)--干掉终点时间在某记录起止时间之间的
SELECT a.name,a.endtime
FROM @t a LEFT OUTER JOIN @t b
ON a.name=b.name and a.endtime>=b.starttime AND a.endtime<b.endtime GROUP BY a.name,a.endtime HAVING COUNT(b.starttime)=0SELECT x.name,x.starttime,MIN(y.endtime)FROM
(SELECT a.name,a.starttime
FROM @t a LEFT OUTER JOIN @t bON a.name=b.name and a.starttime>b.starttime AND a.starttime<=b.endtime GROUP BY a.name,a.starttime HAVING(COUNT(b.starttime)=0)
) x INNER JOIN  (SELECT a.name,a.endtime FROM @t a LEFT OUTER JOIN @t bON a.name=b.name and a.endtime>=b.starttime AND a.endtime<b.endtime GROUP BY a.name,a.endtime HAVING COUNT(b.starttime)=0
) y ON x.name = y.name and x.starttime<=y.endtime GROUP BY x.name,x.starttime;

2.合并SQL中的时间重叠的记录方法:B

起点和起点可以相同，终点和终点可以相同，去掉两值之间数据

declare @t table (Name varchar(100),  starttime  datetime,  endtime datetime);
insert into @t values('A'   , '2017-01-02 00:00' , '2017-03-28 00:10'),('A'   , '2017-05-14 23:50' , '2017-05-29 23:50'),('B'   , '2017-05-18 00:00' , '2017-05-18 04:00'),('B'   , '2017-05-18 02:00' , '2017-05-18 03:00'),('C'   , '2017-01-02 00:00' , '2017-01-17 15:50'),('C'   , '2017-01-14 03:50' , '2017-01-28 15:50');--剔除重复时间select d.Name,d.starttime,endtime=min(d.endtime) from (select a.name,a.starttime,b.endtime from @t a,@t b,@t cwhere a.endtime<=b.endtime and a.Name=b.Name and b.Name=c.Namegroup by a.name,a.starttime,b.endtimehaving(max(case when (a.starttime>c.starttime and a.starttime<=c.endtime) or (b.endtime>=c.starttime and b.endtime<c.endtime) then 1 else 0 end )=0)) dgroup by d.name,d.starttimeorder by d.Name,d.starttime

3.获取SQL中的时间重叠的记录

问题描述

时间重叠指上下两行数据的时间段有重叠部分，现在要找出这些在时间上有重叠的记录。

具体问题

有7个会议室，每个会议室每天都有人开会，某一天的开会时间如下：

查询出开会时间有重叠的是哪几个会议室？上面预期结果是 ID 2 3 4 5 6

问题分析

为了方便分析，我们画了如下一个草图来具体描述。

图中上面部分t和下面部分b有一段是重复的，分别是b.starttime到t.endtime部分。通过数学集合的思想，我们可以得出这个重叠部分的集合关系。

t.starttime<=b.endtime

AND t.endtime>=b.starttime

上面这个数学集合的重叠部分就是我们要的找的。

具体解法

--创建测试数据
WITH Meeting AS(
SELECT 1 ID,'08:00' Starttime,'09:15' Endtime
UNION ALL
SELECT 2,'13:20','15:20'
UNION ALL
SELECT 3,'10:00','14:00'
UNION ALL
SELECT 4,'13:55','16:25'
UNION ALL
SELECT 5,'14:00','17:45'
UNION ALL
SELECT 6,'14:05','17:45'
UNION ALL
SELECT 7,'18:05','19:45')--查询代码
SELECT DISTINCT b.* FROM Meeting t
JOIN Meeting b ON 
t.Starttime<=b.Endtime
AND t.Endtime>=b.Starttime
AND b.ID <> t.ID --排除与自身时间相等的值