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');goselect 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.0002011-09-08 00:20:53.040 2011-09-08 00:20:57.200(2 行受影响)*/godrop table car