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 --排除与自身时间相等的值