假设这个表：
ID Time
1 2013-06-24 14:08:42.000
1 2013-06-25 15:09:45.000
1 2013-06-24 14:10:43.000
2 2013-07-09 10:56:25.000
2 2013-07-09 10:58:25.000
1 2013-06-26 14:10:43.000
1 2013-06-26 14:12:43.000
4 2013-07-31 09:07:26.000
5 2013-07-31 09:07:26.000
ID相同两个值之间的时间间隔超过300秒就算不重复。
第一条和第三条两个id为1的数据以及两个id为2还有第六条和第七条id为1的都算重复。重复的算一条。
所以不重复的总条数就是6。
sql语句应该怎么写？
------解决方案--------------------
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
create table [ta] ([ID] int,[Time] datetime)
insert into [ta]
select 1,'2013-06-24 14:08:42.000' union all
select 1,'2013-06-25 15:09:45.000' union all
select 1,'2013-06-24 14:10:43.000' union all
select 2,'2013-07-09 10:56:25.000' union all
select 2,'2013-07-09 10:58:25.000' union all
select 1,'2013-06-26 14:10:43.000' union all
select 1,'2013-06-26 14:12:43.000' union all
select 4,'2013-07-31 09:07:26.000' union all
select 5,'2013-07-31 09:07:26.000'
;with wang as
(select rowid=ROW_NUMBER() over(order by getdate()),* from ta)
select * from wang
except
select * from wang s
where exists (select 1 from wang t where ID=s.ID and DATEDIFF(ss,s.time,t.time) between 0 and 300 and rowid>s.rowid)
2 1 2013-06-25 15:09:45.000
3 1 2013-06-24 14:10:43.000
5 2 2013-07-09 10:58:25.000
7 1 2013-06-26 14:12:43.000
8 4 2013-07-31 09:07:26.000
9 5 2013-07-31 09:07:26.000