求一sql语句， 两条记录间隔15分钟

Question

declare @dt datetime
set @dt='2014-09-01 10:10:36'
select *
from tb
where 时间 between between @dt and DATEADD(minute,15,@dt)

Answer 1

假定 ID 是连续的。
SELECT t.*
  FROM test t
 WHERE EXISTS (SELECT *
                 FROM test t1
                WHERE t1.id = t.id-1
                  AND t1.recivetime <= DateAdd(minute,-15,t.recivetime)
              )
    OR EXISTS (SELECT *
                 FROM test t2
                WHERE t2.id = t.id+1
                  AND t2.recivetime >= DateAdd(minute,15,t.recivetime)
              )

Answer 2

--下面是递归写法（遍历浪费太多，因为递归里不让用TOP...只能这么凑合），强烈不建议这么写，你还是自己写个循环吧，性能都比我这写的好。不过还是辛苦写出来的，贴出来给大家看看。
declare @arg date ='2014-9-10';
with data(id,lon,lat,recivetime) as(
select 1,110.1,30,CONVERT(datetime,'2014/9/10 0:4:16') union all
select 2,110.1,30,'2014/9/10 0:4:49' union all
select 3,110.1,30,'2014/9/10 0:5:14' union all
select 4,110.1,30,'2014/9/10 0:12:34' union all
select 5,110.1,30,'2014/9/10 0:17:26' union all
select 6,110.1,30,'2014/9/10 0:17:48' union all
select 7,110.1,30,'2014/9/10 0:18:43' union all
select 8,110.1,30,'2014/9/10 0:19:28' union all
select 9,110.1,30,'2014/9/10 0:19:53' union all
select 10,110.1,30,'2014/9/10 0:19:59' union all
select 11,110.1,30,'2014/9/10 0:20:3' union all
select 12,110.1,30,'2014/9/10 0:24:27' union all
select 13,110.1,30,'2014/9/10 0:25:18' union all
select 14,110.1,30,'2014/9/10 0:27:27' union all
select 15,110.1,30,'2014/9/10 0:28:1' union all
select 16,110.1,30,'2014/9/10 0:30:58' union all
select 17,110.1,30,'2014/9/10 0:31:1' union all
select 18,110.1,30,'2014/9/10 0:31:43' union all
select 19,110.1,30,'2014/9/10 0:31:57' union all
select 20,110.1,30,'2014/9/10 0:33:23' union all
select 21,110.1,30,'2014/9/10 0:35:37' union all
select 22,110.1,30,'2014/9/10 0:37:20' union all
select 23,110.1,30,'2014/9/10 0:37:33' union all
select 24,110.1,30,'2014/9/10 0:37:35' union all
select 25,110.1,30,'2014/9/10 0:38:2' union all
select 26,110.1,30,'2014/9/10 0:38:7' union all
select 27,110.1,30,'2014/9/10 0:39:12' union all
select 28,110.1,30,'2014/9/10 0:39:40' union all
select 29,110.1,30,'2014/9/10 0:42:3' union all
select 30,110.1,30,'2014/9/10 0:43:10' union all
select 31,110.1,30,'2014/9/10 0:43:31' union all
select 32,113.9,30,'2014/9/10 0:46:2' union all
select 33,110.1,30,'2014/9/10 0:46:58' union all
select 34,110.1,30,'2014/9/10 0:46:59' union all
select 35,110.1,30,'2014/9/10 0:48:36' union all
select 36,110.1,30,'2014/9/10 0:49:31' union all
select 37,110.1,30,'2014/9/10 0:51:9' union all
select 38,110.1,30,'2014/9/10 0:53:6' union all
select 39,110.1,30,'2014/9/10 0:54:3' union all
select 40,110.1,30,'2014/9/10 0:54:11' union all
select 41,110.1,30,'2014/9/10 0:55:22' union all
select 42,110.1,30,'2014/9/10 0:55:59' union all
select 43,110.1,30,'2014/9/10 0:56:57' union all
select 44,110.1,30,'2014/9/10 0:57:25' union all
select 45,110.1,30,'2014/9/10 0:58:4' union all
select 46,110.1,30,'2014/9/10 0:58:28' union all
select 47,110.1,30,'2014/9/10 0:58:30' union all
select 48,110.1,30,'2014/9/10 1:2:24' union all
select 49,110.1,30,'2014/9/10 1:3:3' union all
select 50,110.1,30,'2014/9/10 1:3:42' union all
select 51,113.1,29.1,'2014/9/10 1:3:53' union all
select 52,110.1,30,'2014/9/10 1:6:2' union all
select 53,110.1,30,'2014/9/10 1:6:32' union all
select 54,110.1,30,'2014/9/10 1:7:44' union all
select 55,110.1,30,'2014/9/10 1:9:25' union all
select 56,118.2,29,'2014/9/10 1:9:41' 
)
,t as (
select *,ROW_NUMBER() over(order by recivetime) sort
from data where CAST(recivetime as date)=@arg
)
,cte as(
select *,1 lvl from t where sort=1
union all
select tt.* ,c1.lvl+1
from cte c1
cross apply(select * from t where t.recivetime>=DATEADD(MINUTE,15,c1.recivetime)) tt
)
select distinct *
from cte c1
where not exists(select 1 from cte c2 where c2.lvl=c1.lvl and c2.sort<c1.sort)
OPTION (MAXRECURSION 96)

Answer 3

不连续可以先用开窗函数 把test 重新编排一下，按照 设备编号分组，时间降序或者升序排，然后在用4楼的方法！

Answer 4

WITH T AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY 设备号
                              ORDER BY recivetime
                             ) n
      FROM test
)
SELECT t.*
  FROM t
 WHERE EXISTS (SELECT *
                 FROM t t1
                WHERE t1.设备号 = t.设备号
                  AND t1.n = t.n-1
                  AND t1.recivetime <= DateAdd(minute,-15,t.recivetime)
              )
    OR EXISTS (SELECT *
                 FROM t t2
                WHERE t2.设备号 = t.设备号
                  AND t2.n = t.n+1
                  AND t2.recivetime >= DateAdd(minute,15,t.recivetime)
              )

Answer 5

  WITH T AS (
    SELECT UserID, Longitude,Latitude,receivetime,
           ROW_NUMBER() OVER (PARTITION BY userid
                              ORDER BY receivetime
                             ) n
      FROM [dbo].[tbLocationHistory20140910] where UserID='0' 
)
SELECT t.*
  FROM t这是我执行上面的语句查到的我需要查询的设备的所有记录。
但是继续使用上面的语句，没有出来结果

Answer 6

我明白了，是我没有表述清楚我的需求，  让大家误解了， 
重新上一些数据，好描述      ID               Longitude                        Latitude       receivetime                         n
13775221 114.825736666667 30.4989133333333 2014-09-10 00:00:23.000 1
13775221 114.825508333333 30.4970333333333 2014-09-10 00:01:24.000 2
13775221 114.825173333333 30.4951566666667 2014-09-10 00:02:23.000 3
13775221 114.825006666667 30.4942216666667 2014-09-10 00:02:53.000 4
13775221 114.824853333333 30.4932033333333 2014-09-10 00:03:25.000 5
13775221 114.82401 30.48173 2014-09-10 00:09:23.000 6
13775221 114.823893333333 30.4808233333333 2014-09-10 00:09:53.000 7
13775221 114.828243333333 30.4421616666667 2014-09-10 00:30:55.000 8
13775221 114.832366666667 30.4359766666667 2014-09-10 00:34:52.000 9
13775221 115.038436666667 30.394655 2014-09-10 02:05:19.000 10
13775221 115.040711666667 30.3931133333333 2014-09-10 02:06:18.000 11
13775221 115.048828333333 30.38732 2014-09-10 02:09:49.000 12
13775221 115.05123 30.38576 2014-09-10 02:10:48.000 13
13775221 115.05241 30.3849483333333 2014-09-10 02:11:17.000 14
13775221 115.055828333333 30.3823633333333 2014-09-10 02:12:48.000 15
13775221 115.056865 30.38141 2014-09-10 02:13:18.000 16
13775221 115.05792 30.3804266666667 2014-09-10 02:13:49.000 17
13775221 115.05881 30.3794766666667 2014-09-10 02:14:17.000 18
13775221 115.059791666667 30.3783883333333 2014-09-10 02:14:50.000 19
13775221 115.061503333333 30.37633 2014-09-10 02:15:48.000 20
13775221 115.063175 30.374175 2014-09-10 02:16:48.000 21
13775221 115.06407 30.3730833333333 2014-09-10 02:17:19.000 22
13775221 115.064903333333 30.3721233333333 2014-09-10 02:17:48.000 23
13775221 115.066736666667 30.37007 2014-09-10 02:18:48.000 24
13775221 115.066737766667 30.37008 2014-09-10 02:25:48.000 25
上面符合条件的是      n=1  n=8  n=10  n=25第一条符合条件，在第一条的基础上，加15分钟，接收时间>00:15:23.000   之后的第一条就是符合条件的，那就是 n=8的那条，   然后是在符合条件的第8条的时间上加15分钟 即接收时间> 00:45:55.000 的第一条也是符合条件的，即n=10   依次论推

Answer 7

WITH T(UserID,Longitude,Latitude,receivetime,n) AS (
    SELECT 13775221,114.825736666667,30.4989133333333,'2014-09-10 00:00:23.000',1 UNION ALL
    SELECT 13775221,114.825508333333,30.4970333333333,'2014-09-10 00:01:24.000',2 UNION ALL
    SELECT 13775221,114.825173333333,30.4951566666667,'2014-09-10 00:02:23.000',3 UNION ALL
    SELECT 13775221,114.825006666667,30.4942216666667,'2014-09-10 00:02:53.000',4 UNION ALL
    SELECT 13775221,114.824853333333,30.4932033333333,'2014-09-10 00:03:25.000',5 UNION ALL
    SELECT 13775221,114.82401,30.48173,'2014-09-10 00:09:23.000',6 UNION ALL
    SELECT 13775221,114.823893333333,30.4808233333333,'2014-09-10 00:09:53.000',7 UNION ALL
    SELECT 13775221,114.828243333333,30.4421616666667,'2014-09-10 00:30:55.000',8 UNION ALL
    SELECT 13775221,114.832366666667,30.4359766666667,'2014-09-10 00:34:52.000',9 UNION ALL
    SELECT 13775221,115.038436666667,30.394655,'2014-09-10 02:05:19.000',10 UNION ALL
    SELECT 13775221,115.040711666667,30.3931133333333,'2014-09-10 02:06:18.000',11 UNION ALL
    SELECT 13775221,115.048828333333,30.38732,'2014-09-10 02:09:49.000',12 UNION ALL
    SELECT 13775221,115.05123,30.38576,'2014-09-10 02:10:48.000',13 UNION ALL
    SELECT 13775221,115.05241,30.3849483333333,'2014-09-10 02:11:17.000',14 UNION ALL
    SELECT 13775221,115.055828333333,30.3823633333333,'2014-09-10 02:12:48.000',15 UNION ALL
    SELECT 13775221,115.056865,30.38141,'2014-09-10 02:13:18.000',16 UNION ALL
    SELECT 13775221,115.05792,30.3804266666667,'2014-09-10 02:13:49.000',17 UNION ALL
    SELECT 13775221,115.05881,30.3794766666667,'2014-09-10 02:14:17.000',18 UNION ALL
    SELECT 13775221,115.059791666667,30.3783883333333,'2014-09-10 02:14:50.000',19 UNION ALL
    SELECT 13775221,115.061503333333,30.37633,'2014-09-10 02:15:48.000',20 UNION ALL
    SELECT 13775221,115.063175,30.374175,'2014-09-10 02:16:48.000',21 UNION ALL
    SELECT 13775221,115.06407,30.3730833333333,'2014-09-10 02:17:19.000',22 UNION ALL
    SELECT 13775221,115.064903333333,30.3721233333333,'2014-09-10 02:17:48.000',23 UNION ALL
    SELECT 13775221,115.066736666667,30.37007,'2014-09-10 02:18:48.000',24 UNION ALL
    SELECT 13775221,115.066737766667,30.37008,'2014-09-10 02:25:48.000',25  UNION ALL
    -- 加几条另外一个 UserID 的记录
    SELECT 13775222,114.825736666667,30.4989133333333,'2014-09-10 00:00:23.000',1 UNION ALL
    SELECT 13775222,114.825508333333,30.4970333333333,'2014-09-10 00:01:24.000',2 UNION ALL
    SELECT 13775222,114.825173333333,30.4951566666667,'2014-09-10 00:20:23.000',3 UNION ALL
    SELECT 13775222,114.824853333333,30.4932033333333,'2014-09-10 00:23:25.000',4
),
cte AS (
    SELECT *
      FROM T
     WHERE n=1
    UNION ALL
    SELECT T.*
      FROM cte
      JOIN T
        ON cte.UserID = T.userID
       AND T.receivetime >= DateAdd(minute,15,cte.receivetime)
     WHERE EXISTS ( -- 递归不能用 TOP、Min()，只能用前一条记录进行判断
                   SELECT *
                     FROM T t0
                    WHERE t0.UserID = T.UserID
                      AND t0.n = T.n-1
                      AND t0.receivetime < DateAdd(minute,15,cte.receivetime)
                   )
)
   SELECT *
    FROM cte
ORDER BY UserID, n
     UserID                               Longitude                                Latitude receivetime                       n
----------- --------------------------------------- --------------------------------------- ----------------------- -----------
   13775221                        114.825736666667                        30.4989133333333 2014-09-10 00:00:23.000           1
   13775221                        114.828243333333                        30.4421616666667 2014-09-10 00:30:55.000           8
   13775221                        115.038436666667                        30.3946550000000 2014-09-10 02:05:19.000          10
   13775221                        115.066737766667                        30.3700800000000 2014-09-10 02:25:48.000          25
   13775222                        114.825736666667                        30.4989133333333 2014-09-10 00:00:23.000           1
   13775222                        114.825173333333                        30.4951566666667 2014-09-10 00:20:23.000           3