如有表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
谢谢各位了~
开始时间 结束时间
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 09: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
开始时间 = (
SELECT
MAX(开始时间)
FROM @A B
WHERE 结束时间 <= A.结束时间
AND NOT EXISTS(
SELECT * FROM @A
WHERE B.开始时间 <= 结束时间
AND B.结束时间 > 结束时间)
),
结束时间
FROM @A A
WHERE NOT EXISTS(
SELECT * FROM @A
WHERE A.结束时间 >= 开始时间
AND A.开始时间 < 开始时间)-- 结果:
开始时间 结束时间
----------------------- -----------------------
2007-01-01 07: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 行受影响)
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' 再次麻烦
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 09: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 * FROM
(
(SELECT 开始时间 FROM @A WHERE 开始时间 NOT IN (SELECT 结束时间 FROM @A))A LEFT JOIN
(SELECT 结束时间 FROM @A WHERE 开始时间 NOT IN (SELECT 开始时间 FROM @A))B
ON 开始时间<结束时间
)
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 09: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 开始时间, MIN(结束时间) FROM
(
(SELECT 开始时间 FROM @A WHERE 开始时间 NOT IN (SELECT 结束时间 FROM @A))A LEFT JOIN
(SELECT 结束时间 FROM @A WHERE 开始时间 NOT IN (SELECT 开始时间 FROM @A))B
ON 开始时间<结束时间
)
GROUP BY 开始时间
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 行受影响)
如
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'
谢谢
开始时间 datetime,
结束时间 datetime
)
INSERT @A
SELECT '2007-01-01 7:00:00', '2007-01-01 9:00:00' UNION ALL
SELECT '2007-01-01 09: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 开始时间, MIN(结束时间) AS 结束时间 FROM
(
(SELECT 开始时间 FROM @A WHERE 开始时间 NOT IN (SELECT 结束时间 FROM @A))A LEFT JOIN
(SELECT 结束时间 FROM @A WHERE 结束时间 NOT IN (SELECT 开始时间 FROM @A))B
ON 开始时间<结束时间
)
GROUP BY 开始时间
开始时间 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 = 1update #
set @i = case when 开始时间 between @begin and @end then @i else @i+1 end
,@begin = 开始时间,@end = 结束时间,group_id = @iselect 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)
*/