表1
id name type holiday socre
1 a 1 360 null
2 b 2 100 null
3 c 3 120 null表type
id name minday maxday
1 x 6 10
2 y 8 16
3 z 10 20说明:表1中的type和表type是外键关系。我想通过存储过程更新表1的socre字段的所有值。条件是根据每个人的type确定出
每个人的最小天数和最大天数,然后根据holiday除以365得到个数和最小天数相加赋给socre谢过了....
id name type holiday socre
1 a 1 360 null
2 b 2 100 null
3 c 3 120 null表type
id name minday maxday
1 x 6 10
2 y 8 16
3 z 10 20说明:表1中的type和表type是外键关系。我想通过存储过程更新表1的socre字段的所有值。条件是根据每个人的type确定出
每个人的最小天数和最大天数,然后根据holiday除以365得到个数和最小天数相加赋给socre谢过了....
go
create table [t1]([id] int,[name] varchar(1),[type] int,[holiday] int,[score] int)
insert [t1]
select 1,'a',1,360,null union all
select 2,'b',2,100,null union all
select 3,'c',3,120,null
go
if object_id('[type]') is not null drop table [type]
go
create table [type]([id] int,[name] varchar(1),[minday] int,[maxday] int)
insert [type]
select 1,'x',6,10 union all
select 2,'y',8,16 union all
select 3,'z',10,20
gocreate proc sp_test
as
update a
set score=a.holiday/360+b.minday
from t1 a,[type] b
where a.[type]=b.id
and a.holiday/360+b.minday<=b.maxday
go--用定时作业设定每月执行一次 exec sp_testselect * from t1
as
update a
set score=a.holiday/365+b.minday
from t1 a,[type] b
where a.[type]=b.id
and a.holiday/365+b.minday<=b.maxday
go这个OK?