现在有一个表brushtime,有字段empno(数据类型char(20)),btime(数据类型(datetime))empno btime
01 1900-01-01 07:52:21.000
01 1900-01-01 07:52:32.000
01 1900-01-01 07:52:45.000
01 1900-01-01 07:52:57.000
现在有个test表,有字段empname(数据类型char(20)),btime(数据类型(datetime))empno btime
01 test表一开始已经有员工编号
目标:现在要将burshtime里面员工01的最初始刷卡时间更新到test表:
更新结果是:empno btime
01 1900-01-01 07:52:21.000
update test
set test.btime=brushtime.btime
from brushtime
where (btime between 1900-01-01 07:00:00.000 and 1900-01-01 09:00:00.000)
and (test.btime>brushtime.btime or test.btime is null)上面更新语句:通过(test.btime>brushtime.btime or test.btime is null)来获取最开始时间。不过得到结果还是
1900-01-01 07:52:57.000
问题:请问是不是我思路错了?为什么得到结果是1900-01-01 07:52:57.000?
而不是我想得到的最初1900-01-01 07:52:21.000?
01 1900-01-01 07:52:21.000
01 1900-01-01 07:52:32.000
01 1900-01-01 07:52:45.000
01 1900-01-01 07:52:57.000
现在有个test表,有字段empname(数据类型char(20)),btime(数据类型(datetime))empno btime
01 test表一开始已经有员工编号
目标:现在要将burshtime里面员工01的最初始刷卡时间更新到test表:
更新结果是:empno btime
01 1900-01-01 07:52:21.000
update test
set test.btime=brushtime.btime
from brushtime
where (btime between 1900-01-01 07:00:00.000 and 1900-01-01 09:00:00.000)
and (test.btime>brushtime.btime or test.btime is null)上面更新语句:通过(test.btime>brushtime.btime or test.btime is null)来获取最开始时间。不过得到结果还是
1900-01-01 07:52:57.000
问题:请问是不是我思路错了?为什么得到结果是1900-01-01 07:52:57.000?
而不是我想得到的最初1900-01-01 07:52:21.000?
(SELECT empno, MAX(BTIME) BTIME
FROM brushtime GROUP BY empno)AS T WHERE TEST.empno=T.empno
(SELECT empno, MIN(BTIME) BTIME
FROM brushtime GROUP BY empno)AS T WHERE TEST.empno=T.empno
set btime=A.btime
from test,
(select empno ,min(btime) as btime from brushtime
where btime between '1900-01-01 07:00:00' and '1900-01-01 09:00:00'
group by empno) A
where test.empno=A.empno/*
or
*/
update test
set btime=(select top 1 btime from brushtime where empno=test.empno
and btime between '1900-01-01 07:00:00' and '1900-01-01 09:00:00'
order by btime )
if object_id('[brushtime]') is not null drop table [brushtime]
create table [brushtime]([empno] varchar(20),[btime] datetime)
insert [brushtime]
select '01','1900-01-01 07:52:21.000' union all
select '01','1900-01-01 07:52:32.000' union all
select '01','1900-01-01 07:52:45.000' union all
select '01','1900-01-01 07:52:57.000'
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([empno] varchar(20),[btime] datetime)
insert [test]
select '01',nullupdate t
set btime = (select min(btime) from [brushtime] where [empno]=t.[empno])
from [test] tselect * from [test]
--------------------------
01 1900-01-01 07:52:21.000