请问怎么计算前后两条数据时间差并统计

请教如何计算前后两条数据时间差并统计
CREATE TABLE [dbo].[CAR](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SPEED] [float] NULL,
[TIME] [datetime] NULL
)

INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.760);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.800);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.840);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.880);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.920);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.960);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:50.000);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.840);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.880);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.920);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.960);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:52.000);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:06:52.000);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:53.040);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:54.080);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:55.120);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:56.160);
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:57.200);

上面的是建一个测试表，预期得到的结果是，根据TIME字段，算出前后两天数据相差不超过5分钟的一共有多少组数据

预期结果是
STARTTIME ENDTIME
2011-09-08 00:05:49.760 2011-09-08 00:05:51.960
2011-09-08 00:20:53.040 2011-09-08 00:20:57.200

------解决方案--------------------

SQL code

CREATE TABLE [dbo].[CAR](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SPEED] [float] NULL,
[TIME] [datetime] NULL
)

INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.760');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.800');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.840');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.880');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.920');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:49.960');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:50.000');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.840');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.880');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.920');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:51.960');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:05:52.000');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:06:52.000');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:53.040');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:54.080');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:55.120');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:56.160');
INSERT INTO [CAR]([SPEED],[TIME]) VALUES(1.1,'2011-09-08 00:20:57.200');
go
select a.time as startdate,b.time as enddate from (
select *,ROW_NUMBER()over(order by time)rn from CAR a where not exists (
select 1 from CAR where (time<a.time and DATEDIFF(mi,time,a.time)<5))
)a inner join (
select *,ROW_NUMBER()over(order by time)rn from CAR a where not exists (
select 1 from CAR where (time>a.time and DATEDIFF(mi,a.time,time)<5))
)b on a.rn=b.rn
/*
startdate               enddate
----------------------- -----------------------
2011-09-08 00:05:49.760 2011-09-08 00:06:52.000
2011-09-08 00:20:53.040 2011-09-08 00:20:57.200

(2 行受影响)

*/
go
drop table car

请问怎么计算前后两条数据时间差并统计

相关推荐