在SQL Server中合并重叠日期

bestyellow

已于 2022-10-17 09:07:13 修改

阅读量2.1k

点赞数 2

分类专栏： sqlserver 文章标签： sql 数据库

于 2022-10-16 10:07:01 首次发布

本文链接：https://blog.csdn.net/bestyellow/article/details/127344305

版权

sqlserver 专栏收录该内容

3 篇文章

订阅专栏

本文介绍两种合并SQL中时间重叠记录的方法，并提供查询时间重叠记录的具体解决方案。方法A适用于去除时间范围内多余的起始和结束时间点；方法B则是在时间完全相同的情况下去除两值之间的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)=0

SELECT x.name,x.starttime,MIN(y.endtime)FROM
(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)
) x 
   INNER JOIN  
 (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)=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 c
          where a.endtime<=b.endtime and a.Name=b.Name and b.Name=c.Name
          group by a.name,a.starttime,b.endtime
           having(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)
        ) d
    group by d.name,d.starttime
    order 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 --排除与自身时间相等的值