34,590
社区成员
发帖
与我相关
我的任务
分享
mysql> create tb_employee(userid varchar(100),StartDate datetime,WorkedAge float);
insert into tb_employee(userID,StartDate)values('0001','2015-05-05');
---------------
create event `UpdateWorkedMonth` on schedule every 1 day starts '2016-11-01 01:10:00'
do update tb_employee set WorkedAge=TIMESTAMPDIFF(MONTH,StartDate, curdate())/12;
drop table tt;
create table tt(c date) ;
insert into tt values('2016-01-01');
alter table tt
add per as cast(datediff(month,c,getdate())*1.0/12 as numeric(8,1));
select * from tt
/*
c per
2016-01-01 0.8
*/
UPDATE e SET e.workmonth= round(DATEDIFF(mm,'5/5/2015',GETDATE())/12.0,1)--=1.5
FROM employee e
WHERE e.workmonth!=round(DATEDIFF(mm,'5/5/2015',GETDATE())/12.0,1)
mysql> create tb_employee(userid varchar(100),StartDate datetime,WorkedAge float);
insert into tb_employee(userID,StartDate)values('0001','2015-05-05');
---------------
create event `UpdateWorkedMonth` on schedule every 1 day starts '2016-11-01 01:10:00'
do update tb_employee set WorkedAge=TIMESTAMPDIFF(MONTH,StartDate, curdate())/12;
[/quote]
CREATE EVENT test
ON SCHEDULE EVERY 1 SECOND starts '2016-10-31 15:06:00'
DO UPDATE position SET Exp_CAE=Exp_CAE+ round(TIMESTAMPDIFF(MONTH,'2016-09-30',curdate())/12.0,2)
我试了这样的,sql语句成功执行了,但是数据并没有更新啊[/quote]
这是创建的一个job,到你设定的时间才运行,你将时间设定的离你当前时间近点-- drop table tab
create table tab(
id int identity(1,1),
birthDate datetime,
age as datediff(YEAR,birthDate,GETDATE()))
go
insert into tab(birthDate)
select '2001-05-15' union all
select '1969-01-11' union all
select '2000-08-01' union all
select '1988-10-25' union all
select '2012-05-12'
go
select * from tab
id birthDate age
-- ----------------------- ----
1 2001-05-15 00:00:00.000 15
2 2001-05-15 00:00:00.000 15
3 1969-01-11 00:00:00.000 47
4 2000-08-01 00:00:00.000 16
5 1988-10-25 00:00:00.000 28
计算列若标记为 PERSISTED,才能参与 FOREIGN KEY 或 CHECK 约束
mysql> create tb_employee(userid varchar(100),StartDate datetime,WorkedAge float);
insert into tb_employee(userID,StartDate)values('0001','2015-05-05');
---------------
create event `UpdateWorkedMonth` on schedule every 1 day starts '2016-11-01 01:10:00'
do update tb_employee set WorkedAge=TIMESTAMPDIFF(MONTH,StartDate, curdate())/12;
[/quote]
CREATE EVENT test
ON SCHEDULE EVERY 1 SECOND starts '2016-10-31 15:06:00'
DO UPDATE position SET Exp_CAE=Exp_CAE+ round(TIMESTAMPDIFF(MONTH,'2016-09-30',curdate())/12.0,2)
我试了这样的,sql语句成功执行了,但是数据并没有更新啊
mysql> create tb_employee(userid varchar(100),StartDate datetime,WorkedAge float);
insert into tb_employee(userID,StartDate)values('0001','2015-05-05');
---------------
create event `UpdateWorkedMonth` on schedule every 1 day starts '2016-11-01 01:10:00'
do update tb_employee set WorkedAge=TIMESTAMPDIFF(MONTH,StartDate, curdate())/12;
[/quote]
CREATE EVENT e_test
ON SCHEDULE EVERY 1 Day
DO UPDATE position SET Exp_CAE=Exp_CAE+ round(DATEDIFF(mm,'2016-09-30',GETDATE())/12.0,2)
这样不知道为什么不对,DATEDIFF报错了,我Exp_CAE存入的是年数2.5,2016-09-30'是该员工工作年数为2.5时的时间,这样加起来就可以了吧,Exp_CAE是用计算得到的年数,并不一定是简单的减去起始时间,所以这个字段我必须存入年数