日期:2014-05-18  浏览次数:20374 次

复杂的时间间隔/分类问题,
表格如下:
time  
9:05
9:06
9:10
9:21
9:22
9:24
9:30
9:31

...
希望得到如下结果
9:05   9:10
9:21   9:24
9:30   9:31

间隔超过5分钟的,划分一组。

------解决方案--------------------
需求不合理,想当然了

为什么不是这样
9:05 9:06 9:10
9:21 9:22 9:24
9:30 9:31



------解决方案--------------------
我以前处理有关刷卡的问题,就是分批处理(如提取某一段时间内的刷卡时间记录,控制行数不超过1w),再使用游标方法,就可以很简单的实现。
如:
CREATE TABLE CTime(OccurTime datetime)
INSERT INTO CTime
SELECT '9:05 ' UNION ALL
SELECT '9:06 ' UNION ALL
SELECT '9:10 ' UNION ALL
SELECT '9:21 ' UNION ALL
SELECT '9:22 ' UNION ALL
SELECT '9:24 ' UNION ALL
SELECT '9:30 ' UNION ALL
SELECT '9:31 '

CREATE TABLE #Result(Time1 datetime,Time2 datetime)
DECLARE @OccurTime datetime
DECLARE @TempTime1 datetime
DECLARE @TempTime2 datetime
DECLARE @Fori int
DECLARE Cursor_Check cursor for
SELECT OccurTime FROM CTime
OPEN Cursor_Check
FETCH NEXT FROM Cursor_Check INTO @OccurTime
SET @Fori=@@CURSOR_ROWS
WHILE @@FETCH_STATUS=0
BEGIN
IF @TempTime1 IS NULL
BEGIN
INSERT INTO #Result SELECT @OccurTime,NULL
SET @TempTime1=@OccurTime
END
ELSE IF DATEDIFF(minute,@TempTime1,@OccurTime)> =5
BEGIN
IF EXISTS(SELECT 1 FROM #Result WHERE Time2 IS NULL)
IF DATEDIFF(minute,@TempTime2,@OccurTime)> =5
BEGIN
UPDATE #Result SET Time2=@TempTime2 WHERE Time2 IS NULL
INSERT INTO #Result SELECT @OccurTime,NULL
END
ELSE
UPDATE #Result SET Time2=@OccurTime WHERE Time2 IS NULL
ELSE
INSERT INTO #Result SELECT @OccurTime,NULL
SET @TempTime1=@OccurTime
PRINT @@CURSOR_ROWS
END
SET @TempTime2=@OccurTime
SET @Fori=@Fori-1
FETCH NEXT FROM Cursor_Check INTO @OccurTime
END
CLOSE Cursor_Check
DEALLOCATE Cursor_Check
UPDATE #Result SET Time2=@OccurTime WHERE Time2 IS NULL
SELECT * FROM #Result
DROP TABLE CTime,#Result