复杂的时间间隔/分类问题，
表格如下：
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
