分段分组

DECLARE @tmp TABLE (id INT,plate VARCHAR(20),time DATETIME,speed INT)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(4,'武装巡逻01','2014-11-05 14:29:06.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(5,'武装巡逻01','2014-11-05 14:29:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(34,'武装巡逻01','2014-11-05 14:34:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(35,'武装巡逻01','2014-11-05 14:34:26.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(36,'武装巡逻01','2014-11-05 14:34:36.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(37,'武装巡逻01','2014-11-05 14:34:46.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(38,'武装巡逻01','2014-11-05 14:34:56.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(39,'武装巡逻01','2014-11-05 14:35:06.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(40,'武装巡逻01','2014-11-05 14:35:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(41,'武装巡逻01','2014-11-05 14:35:26.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(42,'武装巡逻01','2014-11-05 14:35:36.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(43,'武装巡逻01','2014-11-05 14:35:46.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(61,'武装巡逻01','2014-11-05 14:58:26.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(91,'武装巡逻01','2014-11-05 15:03:26.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(99,'武装巡逻01','2014-11-05 15:04:46.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(100,'武装巡逻01','2014-11-05 15:04:56.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(101,'武装巡逻01','2014-11-05 15:05:06.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(102,'武装巡逻01','2014-11-05 15:05:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(103,'武装巡逻01','2014-11-05 15:05:26.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(104,'武装巡逻01','2014-11-05 15:05:36.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(105,'武装巡逻01','2014-11-05 15:05:46.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(106,'武装巡逻01','2014-11-05 15:05:56.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(107,'武装巡逻01','2014-11-05 15:06:06.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(108,'武装巡逻01','2014-11-05 15:06:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(110,'武装巡逻01','2014-11-05 15:10:36.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(111,'武装巡逻01','2014-11-05 15:10:46.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(112,'武装巡逻01','2014-11-05 15:10:56.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(113,'武装巡逻01','2014-11-05 15:11:06.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(114,'武装巡逻01','2014-11-05 15:11:16.000',0)INSERT INTO @tmp(id,plate,TIME,speed) VALUES(115,'武装巡逻01','2014-11-05 15:11:26.000',0)--解法1;WITH t AS (SELECT * FROM @tmp A WHERE EXISTS(SELECT * FROM @tmp B WHERE a.id=b.id-1 )OR (NOT EXISTS(SELECT * FROM @tmp C WHERE a.id=c.id-1)AND NOT EXISTS(SELECT * FROM @tmp D WHERE a.id=d.id+1))),t2 AS (SELECT * FROM t WHERE not EXISTS(SELECT * FROM @tmp e WHERE t.id-1=e.id )),t3 AS (SELECT ROW_NUMBER() OVER (ORDER BY t2.id) AS rowNum,t2.* FROM t2)SELECT rowNum,TIME AS minTime,ISNULL((SELECT MAX(time) FROM @tmp WHERE id>=h.id AND id<(SELECT g.id FROM t3 G WHERE g.rowNum=h.rowNum+1)),(SELECT TOP 1 time FROM @tmp ORDER BY id desc)) AS maxTimeFROM t3 H/*rowNumminTimemaxTime12014-11-05 14:29:06.0002014-11-05 14:29:16.00022014-11-05 14:34:16.0002014-11-05 14:35:46.00032014-11-05 14:58:26.0002014-11-05 14:58:26.00042014-11-05 15:03:26.0002014-11-05 15:03:26.00052014-11-05 15:04:46.0002014-11-05 15:06:16.00062014-11-05 15:10:36.0002014-11-05 15:11:26.000*/--解法2SELECT c.group_id '断点',       MIN(c.time) AS minTime,       MAX(c.time) AS maxTimeFROM   (           SELECT x.id,                  x.time,                  MIN(y.id) group_id           FROM   @tmp x                  LEFT JOIN (                           SELECT id,[TIME] FROM   @tmp a                           WHERE  NOT EXISTS(                                      SELECT 1                                      FROM   @tmp b                                      WHERE  b.id = a.id + 1                                  )                       ) y                       ON  x.id <= y.id           GROUP BY                  x.id,                  x.time       ) cGROUP BY       c.group_id/*断点minTimemaxTime52014-11-05 14:29:06.0002014-11-05 14:29:16.000432014-11-05 14:34:16.0002014-11-05 14:35:46.000612014-11-05 14:58:26.0002014-11-05 14:58:26.000912014-11-05 15:03:26.0002014-11-05 15:03:26.0001082014-11-05 15:04:46.0002014-11-05 15:06:16.0001152014-11-05 15:10:36.0002014-11-05 15:11:26.000*/