Welcome to itblog

Every Day In Every Way I Am Getting Better And Better~

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

--------------------------------
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

---------------------------------
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.日期<日期)

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 distinct a.*
from
@tbl a
where
not exists(select
1
from
@tbl
where
工号=a.工号
and
(datediff(ss,日期,a.日期) between 1 and 300))

mysql获取按日期排序获取最新的记录

2013-04-26 23:38:17

Sql server 取按日期最大的一条记录

2016-03-21 17:37:28

mysql 取一条离当前时间最近的记录

2017-08-12 09:42:09

mysql获取按日期排序获取最新的记录

2016-06-05 19:00:10

在mysql 中查询时间最大的一条记录

2016-08-12 15:53:44

mysql的自动记录时间

2011-11-28 11:06:50

mysql 如何获取每一组创建时间最小的记录

2017-12-11 17:13:44

MySQL获取距离当前日期最近的记录

2017-03-21 15:42:38

mysql字段默认为写记录时间

2018-01-02 16:35:31

mysql 取每个分组中时间最近的记录

2017-11-17 17:36:59