求Sql 语句 时间段的合并

[振乾]

IF OBJECT_ID('DatetimeList') IS NOT  NULL 
  DROP TABLE DatetimeList
CREATE TABLE DatetimeList
(
	TIME DATETIME NOT NULL
)
INSERT INTO DatetimeList VALUES ('2010-06-11 15:31:47.340')
INSERT INTO DatetimeList VALUES ('2010-06-11 15:31:47.340')
INSERT INTO DatetimeList VALUES ('2010-06-11 15:34:47.340') 
INSERT INTO DatetimeList VALUES ('2010-06-11 15:45:47.340')

--查询time 的前5分钟和后一分的时间的时间段(time升序排)
SELECT  DATEADD(minute,-5,dl.[TIME]) TimePre,DATEADD(minute,5,dl.[TIME]) TimeNext FROM DatetimeList dl ORDER BY TIME 

--结果如下
 TimePre                      TimeNext
 2010-06-11 15:26:47.340	2010-06-11 15:36:47.340
 2010-06-11 15:26:47.340	2010-06-11 15:36:47.340
 2010-06-11 15:29:47.340	2010-06-11 15:39:47.340
 2010-06-11 15:40:47.340	2010-06-11 15:50:47.340

--我这里需要将这些时间段中 有重复的或者是交叉着的 一起合并
-- 最终的结果 如下
2010-06-11 15:26:47.340  2010-06-11 15:39:47.340
2010-06-11 15:40:47.340	2010-06-11 15:50:47.340

请大家赐教~

[振乾]

请大家赐教了。~~

[filec75]

友情up

[brownhwy]

加个Group by

[hfCoder]

不懂，帮顶

[永生天地]

SELECT  distinct
case when exists(select 1 from DatetimeList where time<>dl.time and abs(datediff(minute,TIME,dl.TIME))<10 )
	then (select DATEADD(minute,-5, min(TIME)) from DatetimeList where exists(select 1 from DatetimeList where abs(datediff(minute,TIME,dl.TIME))<10 )) 
	else DATEADD(minute,-5,time) end TimePre,
case when exists(select 1 from DatetimeList where time<>dl.time and abs(datediff(minute,TIME,dl.TIME))<10 )
	then (select DATEADD(minute,5,max(TIME)) from DatetimeList where exists(select 1 from DatetimeList where abs(datediff(minute,TIME,dl.TIME))<10 )) 
	else DATEADD(minute,5,time) end TimeNext
FROM DatetimeList dl
/*
TimePre                                                TimeNext                                               
------------------------------------------------------ ------------------------------------------------------ 
2010-06-11 15:26:47.340                                2010-06-11 15:50:47.340
2010-06-11 15:40:47.340                                2010-06-11 15:50:47.340

（所影响的行数为 2 行）

*/ 

[cxmcxm]

每次两条合并，再删除合并后的后一条，一直循环直到无重复

set nocount on
IF OBJECT_ID('tempdb..#DatetimeList') IS NOT  NULL 
  DROP TABLE #DatetimeList
CREATE TABLE #DatetimeList
(
    TIME DATETIME NOT NULL
)
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:31:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:31:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:34:47.340') 
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:45:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:46:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:47:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:50:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:58:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 15:59:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 16:08:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 16:20:47.340')
INSERT INTO #DatetimeList VALUES ('2010-06-11 16:24:47.340')

declare @tmp table (timepre datetime,timenext datetime) --临时保存数据

--查询time 的前5分钟和后一分的时间的时间段(time升序排) ?后5分还是后一分lz的查询是后5分钟
insert into @tmp (timepre,timenext)
SELECT distinct DATEADD(minute,-5,dl.[TIME]) TimePre,DATEADD(minute,5,dl.[TIME]) TimeNext FROM #DatetimeList dl ORDER BY 1
select * from @tmp
while exists(select * from @tmp a,@tmp b where a.timepre<b.timepre and a.timenext>=b.timepre) --有交叉即进行处理
begin
	update a
	set timenext=b.timenext
	from @tmp a,@tmp b
	where a.timepre<b.timepre and a.timenext>=b.timepre --将下一交叉的timenext更新到上一条记录
	--删除timenext相同的记录中timepre更大的记录
	delete a
	from @tmp a,@tmp b
	where a.timenext=b.timenext and a.timepre>b.timepre
end
select * from @tmp

[振乾]

DROP TABLE #temp1
create TABLE #temp1(id INT IDENTITY(1,1) PRIMARY KEY ,timepre datetime,timenext datetime) --临时保存数据  drop table #temp
 
DECLARE @timepre1 DATETIME, @timenext1 DATETIME
declare @timepre2 DATETIME, @timenext2 DATETIME
Declare myCurrsor INSENSITIVE Cursor For       --声明游标 
SELECT timepre, timenext FROM #temp ORDER BY timepre
open myCurrsor             --打开游标
fetch next from myCurrsor into @timepre1, @timenext1
WHILE @@FETCH_STATUS=0
BEGIN 
	fetch next from myCurrsor into @timepre2, @timenext2
	IF (@timenext1 > @timepre2)
	BEGIN
		SET @timenext1 = @timenext2
	END
	ELSE
	BEGIN
		--加入新表
		insert into #temp1 VALues(@timepre1,@timenext1)
		SET @timepre1 = @timepre2
		SET @timenext1 = @timenext2
	END
end 
CLOSE myCurrsor                  --关闭游标 
DEALLOCATE myCurrsor
insert into #temp1 VALues(@timepre1,@timenext1)
SELECT * FROM #temp1