相差一定范围的记录只取一条怎么写SQL
假如是这样的两条记录
ID time
1 2012-10-02 08:16:52.000
2 2012-10-02 08:17:52.000
1 2012-10-02 08:16:54.000
....
像这样两个字段，ID为1的两条记录差2秒，假设我设一个条件，相差10秒以内的记录只取一条为有效记录，应该怎么写这个SQL语句呢？
[最优解释]
declare @T table (ID int,time datetime)
insert into @T
select 1,'2012-10-02 08:16:52.000' union all
select 2,'2012-10-02 08:17:52.000' union all
select 1,'2012-10-02 08:16:54.000' union all
select 2,'2012-10-02 08:17:58.000' union all
select 1,'2012-10-02 08:16:58.000' union all
select 2,'2012-10-02 08:18:05.000' union all
select 1,'2012-10-02 08:17:04.000' union all
select 2,'2012-10-02 08:18:16.000' union all
select 1,'2012-10-02 08:17:08.000' union all
select 2,'2012-10-02 08:18:18.000' union all
select 1,'2012-10-02 08:17:13.000' union all
select 2,'2012-10-02 08:18:19.000' union all
select 1,'2012-10-02 08:17:34.000' union all
select 2,'2012-10-02 08:19:23.000'
;with maco as
(
select *,datediff(s,btime,time) as c1 from
(
select *,(select top 1 time from @T where ID=t.ID) as btime from @T t
) a
)
select ID,time from maco t
where time=(select min(time) from maco where ID=t.ID and c1/10=t.c1/10)
/*
ID time
----------- -----------------------
1 2012-10-02 08:16:52.000
2 2012-10-02 08:17:52.000
2 2012-10-02 08:18:05.000
1 2012-10-02 08:17:04.000
2 2012-10-02 08:18:16.000
1 2012-10-02 08:17:13.000
1 2012-10-02 08:17:34.000
2 2012-10-02 08:19:23.000
(8 row(s) affected)
*/
