--二月份的
update table2 set avg_salary =
( select a.salary + b.salary from table1 a, table2 b
where a.id=table2.id and b.id = table2.id )
--三月分
update table3 set avg_salary =
( select a.salary + b.salary+c.salary from table1 a, table2 b,table3 c
where a.id=table3.id and b.id = table3.id and c.id = table3.id )
--如此等等
最好的建一个动态的视图view,就更加简洁的sql语句了.
比如计算二月的时候,create view view_all
as select * from table1 union * from table2
update table2 set avg_salary = ( select sum(salary) from view_all where id= table2.id);
三月
create view view_all
as select * from table1 union select * from table2 union select * from table3
update table3 set avg_salary = ( select sum(salary) from view_all where id= table3.id);
如此类推继续
十二月
create view view_all
as
select * from table1
union select * from table2
union select * from table3
union select * from table4
union select * from table5
union select * from table6
union select * from table7
union select * from table8
union select * from table9
union select * from table10
union select * from table11
union select * from table12
update table12 set avg_salary = ( select sum(salary) from view_all where id= table12.id);
我用的是oracle 语法,相应的mssql语句应该差不多,可能需要稍微改造