请问怎么计算前后两条数据时间差并统计
日期：2014-05-18 浏览次数：20652 次
请教如何计算前后两条数据时间差并统计
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
------解决方案--------------------
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
