drop table #t1,#t2 create table #t1(GSBH varchar(2),js1 varchar(100),js2 varchar(100),js3 varchar(100),js4 varchar(100)) insert #t1 select '01','100','JS1*0.5','(JS2+JS1)*2','(JS3+Js1)*0.55' union all select '02','200','JS1*0.6','(JS2+JS1)*2.1','(JS3+Js1)*0.58' union all select '03','150','JS1*0.9','(JS2+JS1)*2.5','(JS3+Js1)*0.48' union all select '04','210','JS1*0.2','(JS2+JS1)*1.8','(JS3+Js1)*0.64'create table #t2(BH char(4),GSBH char(2),js1 numeric(10,2),js2 numeric(10,2),js3 numeric(10,2),js4 numeric(10,2)) insert #t2(BH,GSBH) select 'A001','01' union all select 'A002','03' union all select 'A005','04' declare @a char(2),@b varchar(100),@c varchar(100),@d varchar(100),@e varchar(100),@str varchar(1000) declare cur cursor for select GSBH,js1,js2,js3,js4 from #t1 open cur fetch next from cur into @a,@b,@c,@d,@e while @@fetch_status = 0 begin exec('update #t2 set js1='+@b+' where GSBH='''+@a+'''') exec('update #t2 set js2='+@c+' where GSBH='''+@a+'''') exec('update #t2 set js3='+@d+' where GSBH='''+@a+'''') exec('update #t2 set js4='+@e+' where GSBH='''+@a+'''') fetch next from cur into @a,@b,@c,@d,@e end close cur deallocate curSelect * from #t2BH GSBH js1 js2 js3 js4 ---- ---- ------------ ------------ ------------ ------------ A001 01 100.00 50.00 300.00 220.00 A002 03 150.00 135.00 712.50 414.00 A005 04 210.00 42.00 453.60 424.70(所影响的行数为 3 行)
真是不好意思。谢谢txlicenhe(马可)!
create table #你的表(GSBH varchar(10),JS1 int,JS2 varchar(100),JS3 varchar(100),JS4 varchar(100)) insert #你的表 values('01', 100 , 'JS1*0.5', '(JS2+JS1)*2' , '(JS3+JS1)*0.55') insert #你的表 values('02', 200 , 'JS1*0.6' , '(JS2+JS1)*2.1' , '(JS3+JS1)*0.58') insert #你的表 values('03', 150 , 'JS1*0.9' , '(JS2+JS1)*2.5' , '(JS3+JS1)*0.48') insert #你的表 values('04', 210 , 'JS1*0.2' , '(JS2+JS1)*1.8', '(JS3+JS1)*0.64') create table #t2(BH VARCHAR(10),GSBH varchar(10),JS1 int,JS2 numeric(10,2),JS3 numeric(10,2),JS4 numeric(10,2)) insert #t2 (BH,GSBH) values('A001','01') insert #t2 (BH,GSBH) values('A002','03') insert #t2 (BH,GSBH) values('A005','04')declare @a varchar(100),@b varchar(100),@c varchar(100),@d varchar(100),@e varchar(100)declare cur cursor for select * from #你的表 open curfetch next from cur into @a,@b,@c,@d,@ewhile @@fetch_status = 0 begin exec('update #T2 set JS1='+@b+' where GSBH='''+@a+'''') exec('update #T2 set JS2='+@c+' where GSBH='''+@a+'''') exec('update #T2 set JS3='+@d+' where GSBH='''+@a+'''') exec('update #T2 set JS4='+@e+' where GSBH='''+@a+'''') fetch next from cur into @a,@b,@c,@d,@e end close cur deallocate curSelect * from #T2 go drop table #你的表,#T2
要不然你可以写在外面的。
如果按照你的意思,需要把公式先读出来了,可能要动态sql了。
create table #t1(GSBH varchar(2),js1 varchar(100),js2 varchar(100),js3 varchar(100),js4 varchar(100))
insert #t1 select '01','100','JS1*0.5','(JS2+JS1)*2','(JS3+Js1)*0.55'
union all select '02','200','JS1*0.6','(JS2+JS1)*2.1','(JS3+Js1)*0.58'
union all select '03','150','JS1*0.9','(JS2+JS1)*2.5','(JS3+Js1)*0.48'
union all select '04','210','JS1*0.2','(JS2+JS1)*1.8','(JS3+Js1)*0.64'create table #t2(BH char(4),GSBH char(2),js1 numeric(10,2),js2 numeric(10,2),js3 numeric(10,2),js4 numeric(10,2))
insert #t2(BH,GSBH) select 'A001','01'
union all select 'A002','03'
union all select 'A005','04'
declare @a char(2),@b varchar(100),@c varchar(100),@d varchar(100),@e varchar(100),@str varchar(1000)
declare cur cursor for select GSBH,js1,js2,js3,js4 from #t1
open cur
fetch next from cur into @a,@b,@c,@d,@e
while @@fetch_status = 0
begin
exec('update #t2 set js1='+@b+' where GSBH='''+@a+'''')
exec('update #t2 set js2='+@c+' where GSBH='''+@a+'''')
exec('update #t2 set js3='+@d+' where GSBH='''+@a+'''')
exec('update #t2 set js4='+@e+' where GSBH='''+@a+'''')
fetch next from cur into @a,@b,@c,@d,@e
end
close cur
deallocate curSelect * from #t2BH GSBH js1 js2 js3 js4
---- ---- ------------ ------------ ------------ ------------
A001 01 100.00 50.00 300.00 220.00
A002 03 150.00 135.00 712.50 414.00
A005 04 210.00 42.00 453.60 424.70(所影响的行数为 3 行)
insert #你的表 values('01', 100 , 'JS1*0.5', '(JS2+JS1)*2' , '(JS3+JS1)*0.55')
insert #你的表 values('02', 200 , 'JS1*0.6' , '(JS2+JS1)*2.1' , '(JS3+JS1)*0.58')
insert #你的表 values('03', 150 , 'JS1*0.9' , '(JS2+JS1)*2.5' , '(JS3+JS1)*0.48')
insert #你的表 values('04', 210 , 'JS1*0.2' , '(JS2+JS1)*1.8', '(JS3+JS1)*0.64')
create table #t2(BH VARCHAR(10),GSBH varchar(10),JS1 int,JS2 numeric(10,2),JS3 numeric(10,2),JS4 numeric(10,2))
insert #t2 (BH,GSBH) values('A001','01')
insert #t2 (BH,GSBH) values('A002','03')
insert #t2 (BH,GSBH) values('A005','04')declare @a varchar(100),@b varchar(100),@c varchar(100),@d varchar(100),@e varchar(100)declare cur cursor for select * from #你的表
open curfetch next from cur into @a,@b,@c,@d,@ewhile @@fetch_status = 0
begin
exec('update #T2 set JS1='+@b+' where GSBH='''+@a+'''')
exec('update #T2 set JS2='+@c+' where GSBH='''+@a+'''')
exec('update #T2 set JS3='+@d+' where GSBH='''+@a+'''')
exec('update #T2 set JS4='+@e+' where GSBH='''+@a+'''')
fetch next from cur into @a,@b,@c,@d,@e
end
close cur
deallocate curSelect * from #T2
go
drop table #你的表,#T2