1. 爱易网页
  2. MSSQL教程
  3. 请问怎么计算前后两条数据时间差并统计

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

日期：2014-05-18　 浏览次数：20530 次

请教如何计算前后两条数据时间差并统计
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

相关资料更多>

  1. 急求SQL语句！＠解决办法
  2. 无法连接到报表服务器，如何解决
  3. 请问一个有关日期的存储过程
  4. 讲授SQL Server各方面内容最好的书是什么
  5. 新人求引导啊
  6. SQL小白求教解决思路
  7. 怎样在SqlServer中处理chr(9)到有关问题
  8. 游标定义查询语句传参数遇到有关问题
  9. vfp求随机命令解决方法