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

1105人阅读 评论(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

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

0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：262541次
• 积分：2991
• 等级：
• 排名：第12132名
• 原创：62篇
• 转载：53篇
• 译文：0篇
• 评论：50条
评论排行
最新评论