求大神，计算时间差值(从速度大于或等于60km开始计算时间,如果持续大于60km,则时间获取大于或等于60km结束时的时间,

[lideyuans]

[gw6328]

DECLARE @t TABLE(id INT IDENTITY(1,1),dt DATETIME,v int)

INSERT INTO @t SELECT '2013-12-04 16:41:04.027',60 UNION ALL 
SELECT '2013-12-05 16:41:04.027',60 UNION ALL 
SELECT '2013-12-06 16:41:04.027',60 UNION ALL 
SELECT '2013-12-07 16:41:04.027',59 UNION ALL 
SELECT '2013-12-08 16:41:04.027',59 UNION ALL 
SELECT '2013-12-09 16:41:04.027',60 UNION ALL 
SELECT '2013-12-10 16:41:04.027',61 UNION ALL 
SELECT '2013-12-11 16:41:04.027',67 UNION ALL 
SELECT '2013-12-12 16:41:04.027',59 UNION ALL
SELECT '2013-12-13 16:41:04.027',60;

--SELECT * FROM @t;

SELECT a.* FROM @t a LEFT JOIN @t b ON a.id=b.id-1
WHERE ISNULL(b.v,0)<60 AND a.v>=60

/*
id          dt                      v
----------- ----------------------- -----------
3           2013-12-06 16:41:04.027 60
8           2013-12-11 16:41:04.027 67
10          2013-12-13 16:41:04.027 60
*/

[lideyuans]

DECLARE @t TABLE(id INT IDENTITY(1,1),dt DATETIME,v int) INSERT INTO @t SELECT '2013-12-04 16:41:04.027',60 UNION ALL SELECT '2013-12-05 16:41:04.027',60 UNION ALL SELECT '2013-12-06 16:41:04.027',60 UNION ALL SELECT '2013-12-07 16:41:04.027',59 UNION ALL SELECT '2013-12-08 16:41:04.027',59 UNION ALL SELECT '2013-12-09 16:41:04.027',60 UNION ALL SELECT '2013-12-10 16:41:04.027',61 UNION ALL SELECT '2013-12-11 16:41:04.027',67 UNION ALL SELECT '2013-12-12 16:41:04.027',59 UNION ALL SELECT '2013-12-13 16:41:04.027',60; --SELECT * FROM @t; SELECT a.* FROM @t a LEFT JOIN @t b ON a.id=b.id-1 WHERE ISNULL(b.v,0)<60 AND a.v>=60 /* id dt v ----------- ----------------------- ----------- /*我要的效果是这样呀 1,'2013-12-04 16:41:04.027',60 '2013-12-05 16:41:04.027',60 → →有连续的 并且可以求04号和06号之间的时间差 '2013-12-06 16:41:04.027',60 2,'2013-12-09 16:41:04.027',60 '2013-12-10 16:41:04.027',61 → →有连续的 并且可以求09号和11号之间的时间差 '2013-12-11 16:41:04.027',67 */