日期:2014-05-16 浏览次数:20565 次
--> 测试数据: [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