＋设为首页 ＋添加到收藏夹
员工编号 打卡时间
10 2005-8-4 17:11:37
10 2005-8-4 17:15:37
10 2005-8-4 20:11:37
10 2005-8-5 17:11:37
11 2005-8-5 17:11:37
11 2005-8-5 17:15:37
11 2005-8-5 20:11:37
想要得到的结果：选取同一员工编号 在同一日期 打卡时间超过一个小时的打卡时间
例：
10 2005-8-4 17:11:37
10 2005-8-4 20:11:37
10 2005-8-5 17:11:37
11 2005-8-5 17:11:37
11 2005-8-5 20:11:37
declare @tb table( id int,dt datetime)
insert into @tb
select 10, 2005-8-4 17:11:37 union
select 10 , 2005-8-4 17:15:37 union
select 10 , 2005-8-4 20:11:37 union
select 10 , 2005-8-5 17:11:37 union
select 11 , 2005-8-5 17:11:37 union
select 11 , 2005-8-5 17:15:37 union
select 11 , 2005-8-5 20:11:37
-- 查询
select * from @tb t
where not exists(select 1
from @tb
where id=t.id and
dt>t.dt and
day(dt)=day(t.dt) and
datediff(hour,t.dt,dt)<=1)
order by id,dt
--结果
/*
id dt
----------- ------------------------------------------------------
10.00 2005-8-4 17:15
10.00 2005-8-4 20:11
10.00 2005-8-5 17:11
11.00 2005-8-5 17:15
11.00 2005-8-5 20:11
*/
--抱歉。上面应该用升序的。说明是对的，但是代码写成了desc
--测试表及数据
declare @t table( id int,dt datetime)
insert into @t
select 10, 2005-8-4 17:11:37 union
select 10 , 2005-8-4 17:15:37 union
select 10 , 2005-8-4 20:11:37 union
select 10 , 2005-8-5 17:11:37 union
select 11 , 2005-8-5 17:11:37 union
select 11 , 2005-8-5 17:15:37 union
select 11 , 2005-8-5 20:11:37
select id,(select top 1 dt from @t where id=a.id and convert(varchar(13),dt,120)=convert(varchar(13),a.dt,120) order by dt )
from @t a
group by id,convert(varchar(13),a.dt,120)
/*
结果
id dt
-------------------------
10 2005-08-04 17:11:37.000
10 2005-08-04 20:11:37.000
10 2005-08-05 17:11:37.000
11 2005-08-05 17:11:37.000
11 2005-08-05 20:11:37.000
*/
