求合并重复时间的方法
如有表A
开始时间 结束时间
2007-01-01 7:00:00 2007-01-01 9:00:00
2007-01-01 9:00:00 2007-01-01 10:00:00
2007-01-01 13:00:00 2007-01-01 16:00:00
2007-01-01 15:00:00 2007-01-01 18:00:00
2007-01-01 19:00:00 2007-01-01 20:00:00
要求时间有重叠或相连的合并，结果为
开始时间 结束时间
2007-01-01 7:00:00 2007-01-01 10:00:00
2007-01-01 13:00:00 2007-01-01 18:00:00
2007-01-01 19:00:00 2007-01-01 20:00:00
谢谢各位了~
[解决办法]
-- 示例数据
DECLARE @A TABLE(
开始时间 datetime,
结束时间 datetime
)
INSERT @A
SELECT '2007-01-01 7:00:00 ', '2007-01-01 9:00:00 ' UNION ALL
SELECT '2007-01-01 6:00:00 ', '2007-01-01 10:00:00 ' UNION ALL
SELECT '2007-01-01 13:00:00 ', '2007-01-01 16:00:00 ' UNION ALL
SELECT '2007-01-01 15:00:00 ', '2007-01-01 18:00:00 ' UNION ALL
SELECT '2007-01-01 19:00:00 ', '2007-01-01 20:00:00 '
-- 有效数据放入临时表
SELECT
*
INTO #
FROM @A A
WHERE NOT EXISTS(
SELECT * FROM @A
WHERE 开始时间 < A.开始时间
AND 结束时间 > A.结束时间)
SELECT
开始时间 = (
SELECT
MAX(开始时间)
FROM # B
WHERE 结束时间 <= A.结束时间
AND NOT EXISTS(
SELECT * FROM #
WHERE B.开始时间 <= 结束时间
AND B.结束时间 > 结束时间)
),
结束时间
FROM # A
WHERE NOT EXISTS(
SELECT * FROM #
WHERE (A.结束时间 > = 开始时间
AND A.开始时间 < 开始时间)
)
DROP TABLE #
-- 结果:
开始时间 结束时间
----------------------- -----------------------
2007-01-01 06:00:00.000 2007-01-01 10:00:00.000
2007-01-01 13:00:00.000 2007-01-01 18:00:00.000
2007-01-01 19:00:00.000 2007-01-01 20:00:00.000
(3 行受影响)
[解决办法]
DECLARE @A TABLE(
开始时间 datetime,
结束时间 datetime
)
INSERT @A
SELECT '2007-01-01 7:00:00 ', '2007-01-01 9:00:00 ' UNION ALL
SELECT '2007-01-01 6:00:00 ', '2007-01-01 10:00:00 ' UNION ALL
SELECT '2007-01-01 13:00:00 ', '2007-01-01 16:00:00 ' UNION ALL
SELECT '2007-01-01 15:00:00 ', '2007-01-01 18:00:00 ' UNION ALL
SELECT '2007-01-01 9:00:00 ', '2007-01-01 10:00:00 '
select *,0 as group_id into # from @a order by 开始时间,结束时间
declare @begin datetime,@end datetime,@tmp datetime,@i int
set @i = 1
update #
set @i = case when 开始时间 between @begin and @end then @i else @i+1 end
,@begin = 开始时间,@end = 结束时间,group_id = @i
select min(开始时间) as 开始时间,max(结束时间) as 结束时间 from # group by group_id
go
drop table #
/*
开始时间 结束时间
------------------------------------------------------ ------------------------------------------------------
2007-01-01 06:00:00.000 2007-01-01 10:00:00.000
2007-01-01 13:00:00.000 2007-01-01 18:00:00.000
(2 row(s) affected)
*/
