怎样在一个时间段内记录,只取最早的一条记录?

最新推荐文章于 2022-12-08 11:20:11 发布

itblog

最新推荐文章于 2022-12-08 11:20:11 发布

阅读量3.3k

点赞数

分类专栏： SQL Sever 文章标签： insert table

本文链接：https://blog.csdn.net/itblog/article/details/745788

版权

SQL Sever 专栏收录该内容

66 篇文章 0 订阅

订阅专栏

表:

工号日期
--------------------------------
12006-03-15 08:00:40.000
12006-03-15 18:10:35.000
12006-03-15 18:10:36.000
22006-03-15 07:54:45.000
22006-03-15 18:10:03.000
32006-03-15 07:59:46.000
32006-03-15 07:59:47.000
32006-03-15 19:15:30.000
42006-03-15 08:03:47.000
42006-03-15 19:41:08.000
52006-03-15 19:41:09.000

结果(同一工号日期在5分钟范围内的记录,只取最早的一条记录):
工号日期
---------------------------------
12006-03-15 08:00:40.000
12006-03-15 18:10:35.000
22006-03-15 07:54:45.000
22006-03-15 18:10:03.000
32006-03-15 07:59:46.000
32006-03-15 19:15:30.000
42006-03-15 08:03:47.000
42006-03-15 19:41:08.000
52006-03-15 19:41:09.000

declare @tbl table (工号 int,日期 datetime)
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.日期<日期)

select distinct a.*
from
    @tbl a
where
    not exists(select
                   1
               from
                   @tbl
               where
                   工号=a.工号
                   and
                   (datediff(ss,日期,a.日期) between 1 and 300))