表:工号 日期
--------------------------------
1 2006-03-15 08:00:40.000
1 2006-03-15 18:10:35.000
1 2006-03-15 18:10:36.000
2 2006-03-15 07:54:45.000
2 2006-03-15 18:10:03.000
3 2006-03-15 07:59:46.000
3 2006-03-15 07:59:47.000
3 2006-03-15 19:15:30.000
4 2006-03-15 08:03:47.000
4 2006-03-15 19:41:08.000
5 2006-03-15 19:41:09.000结果(同一工号日期在5分钟范围内的记录,只取最早的一条记录):
工号 日期
---------------------------------
1 2006-03-15 08:00:40.000
1 2006-03-15 18:10:35.000
2 2006-03-15 07:54:45.000
2 2006-03-15 18:10:03.000
3 2006-03-15 07:59:46.000
3 2006-03-15 19:15:30.000
4 2006-03-15 08:03:47.000
4 2006-03-15 19:41:08.000
5 2006-03-15 19:41:09.000
--------------------------------
1 2006-03-15 08:00:40.000
1 2006-03-15 18:10:35.000
1 2006-03-15 18:10:36.000
2 2006-03-15 07:54:45.000
2 2006-03-15 18:10:03.000
3 2006-03-15 07:59:46.000
3 2006-03-15 07:59:47.000
3 2006-03-15 19:15:30.000
4 2006-03-15 08:03:47.000
4 2006-03-15 19:41:08.000
5 2006-03-15 19:41:09.000结果(同一工号日期在5分钟范围内的记录,只取最早的一条记录):
工号 日期
---------------------------------
1 2006-03-15 08:00:40.000
1 2006-03-15 18:10:35.000
2 2006-03-15 07:54:45.000
2 2006-03-15 18:10:03.000
3 2006-03-15 07:59:46.000
3 2006-03-15 19:15:30.000
4 2006-03-15 08:03:47.000
4 2006-03-15 19:41:08.000
5 2006-03-15 19:41:09.000
insert into @tbl
select 1 ,'2006-03-15 08:00:40.000'
union select 1 ,'2006-03-15 18:10:35.000'
union select 1 ,'2006-03-15 18:10:36.000'
union select 2 ,'2006-03-15 07:54:45.000'
union select 2 ,'2006-03-15 18:10:03.000'
union select 3 ,'2006-03-15 07:59:46.000'
union select 3 ,'2006-03-15 07:59:47.000'
union select 3 ,'2006-03-15 19:15:30.000'
union select 4 ,'2006-03-15 08:03:47.000'
union select 4 ,'2006-03-15 19:41:08.000'
union select 5 ,'2006-03-15 19:41:09.000'select * from @tbl as a
where not exists(
select * from @tbl where 工号=a.工号 and
abs(datediff(Second,日期,a.日期))<=300 and a.日期<日期)
insert into @tbl
select 1 ,'2006-03-15 08:00:40.000'
union select 1 ,'2006-03-15 18:10:35.000'
union select 1 ,'2006-03-15 18:10:36.000'
union select 2 ,'2006-03-15 07:54:45.000'
union select 2 ,'2006-03-15 18:10:03.000'
union select 3 ,'2006-03-15 07:59:46.000'
union select 3 ,'2006-03-15 07:59:47.000'
union select 3 ,'2006-03-15 19:15:30.000'
union select 4 ,'2006-03-15 08:03:47.000'
union select 4 ,'2006-03-15 19:41:08.000'
union select 5 ,'2006-03-15 19:41:09.000'select distinct a.*
from
@tbl a
where
not exists(select
1
from
@tbl
where
工号=a.工号
and
(datediff(ss,日期,a.日期) between 1 and 300))