--测试表及数据
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 *
from @t a
where exists(select 1 from @t where id=a.id and datediff(hh,a.dt,dt)>=1)
/*
结果
id dt
-------------------------
10 2005-08-04 17:11:37.000
10 2005-08-04 17:15:37.000
10 2005-08-04 20:11:37.000
11 2005-08-05 17:11:37.000
11 2005-08-05 17:15:37.000
*/
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 *
from @t a
where exists(select 1 from @t where id=a.id and datediff(hh,a.dt,dt)>=1)
/*
结果
id dt
-------------------------
10 2005-08-04 17:11:37.000
10 2005-08-04 17:15:37.000
10 2005-08-04 20:11:37.000
11 2005-08-05 17:11:37.000
11 2005-08-05 17:15:37.000
*/
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 desc)
from @t a
group by id,convert(varchar(13),a.dt,120)
/*
结果
id dt
-------------------------
10 2005-08-04 17:15:37.000
10 2005-08-04 20:11:37.000
10 2005-08-05 17:11:37.000
11 2005-08-05 17:15:37.000
11 2005-08-05 20:11:37.000*/
a.*
from
表 a
where
a.打卡时间 = (select top 1 打卡时间 from 表 where 员工编号=a.员工编号 and datediff(dd,打卡时间,a.打卡时间)=0)
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(所影响的行数为 5 行)
*/
我要得:
10 2005-8-4 17:11:37
你给的是
10.00 2005-8-4 17:15
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)都取得是两个相差一个小时之间的晚的打卡时间,不能取得那个早的时间?
10 2005-8-4 17:11:37 10 2005-08-04 17:15:37.000
10 2005-8-4 20:11:37 10 2005-08-04 20:11:37.000
10 2005-8-5 17:11:37 10 2005-08-05 17:11:37.000
11 2005-8-5 17:11:37 11 2005-08-05 17:15:37.000
11 2005-8-5 20:11:37 11 2005-08-05 20:11:37.000
rivery(river),你看看,有两个地方不对 分别是第一个和 第四个
--测试表及数据
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
*/
http://community.csdn.net/Expert/topic/4256/4256773.xml?temp=.266308
from @t a
group by id,convert(varchar(13),a.dt,120)你的查询里没用 datediff ,只用了一个 top1 and 比较了日期相同的,我不明白你既然用了top 1 怎么能在同一日期中取到两个dt的?
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,dt,t.dt)<=1)
order by id,dt--结果
/*id dt
----------- ------------------------------------------------------
10.00 2005-8-4 17:11
10.00 2005-8-4 20:11
10.00 2005-8-5 17:11
11.00 2005-8-5 17:11
11.00 2005-8-5 20:11(所影响的行数为 5 行)
*/