sqlserver计算A表符合某个条件多条数据的某个字段的值的和去更新B表的某个字段
通过临时表来实现
直接用SQL来讲解
--关闭自动提交
SET IMPLICIT_TRANSACTIONS ON
begin
--删除临时表
--drop table ##linshi
--创建临时表
create table ##linshi(cCustomerGUID nvarchar(128),heji decimal(32,6),yingtui decimal(32,6),jisuan decimal(32,6))
--插入3月的ID
insert into ##linshi
select cCustomerGUID,fSumMoney,fSumMoney,fSumMoney from XCX_baoshui where cName='增值税及附加' and iYear='2023' and iMonth='3'
--将明细表的附加税合计匹配ID更新至临时表
update ##linshi set heji=fMoneny1 from ##linshi,XCX_baoshui_qita where ##linshi.cCustomerGUID=XCX_baoshui_qita.cCustomerGUID
and XCX_baoshui_qita.cTextParam1='附加税合计' and XCX_baoshui_qita.iYear='2023' and XCX_baoshui_qita.iMonth='3'
--将明细表的应退补税额匹配ID更新至临时表
update ##linshi set yingtui=fMoneny1 from ##linshi,XCX_baoshui_qita where ##linshi.cCustomerGUID=XCX_baoshui_qita.cCustomerGUID
and XCX_baoshui_qita.cTextParam1='本期应补(退)税额'and XCX_baoshui_qita.iYear='2023' and XCX_baoshui_qita.iMonth='3'
--将合计额度=附加税合计+应退补计算出来更新至临时表
update ##linshi set jisuan=yingtui+heji
select * from ##linshi where cCustomerGUID='1f904053-06dc-494b-afed-86dfe1fd2747'
--将计算好的值更新至目标表
--小程序纳税申报税额
update XCX_baoshui set fSumMoney=jisuan from XCX_baoshui,##linshi where ##linshi.cCustomerGUID=XCX_baoshui.cCustomerGUID
and XCX_baoshui.cName='增值税及附加' and XCX_baoshui.iYear='2023' and XCX_baoshui.iMonth='3'
--税款缴纳列表
update XCX_baoshui set fSumMoney=jisuan from XCX_baoshui,##linshi where ##linshi.cCustomerGUID=XCX_baoshui.cCustomerGUID
and XCX_baoshui.cName='扣缴[增值税及附加]税金' and XCX_baoshui.iYear='2023' and XCX_baoshui.iMonth='3'
--税款缴纳中的明细
update XCX_baoshui_qita set fMoneny1=jisuan from XCX_baoshui_qita,##linshi where ##linshi.cCustomerGUID=XCX_baoshui_qita.cCustomerGUID
and XCX_baoshui_qita.cName='扣缴[增值税及附加]税金' and XCX_baoshui_qita.iYear='2023' and XCX_baoshui_qita.iMonth='3'
--更新进度查询
update XCX_jinduchaxun set cTextParam2=jisuan,cTextParam3=jisuan from XCX_jinduchaxun,##linshi where ##linshi.cCustomerGUID=XCX_jinduchaxun.cCustomerGUID
and XCX_jinduchaxun.cName='扣缴[增值税及附加]税金' and XCX_jinduchaxun.iYear='2023' and XCX_jinduchaxun.iMonth='3'
--查询验证
select fSumMoney,* from XCX_baoshui where cName like'%增值税%' and iYear='2023' and iMonth='3'
and cCustomerGUID='1f904053-06dc-494b-afed-86dfe1fd2747'
select fMoneny1,* from XCX_baoshui_qita where cName like'%增值税%' and iYear='2023' and iMonth='3'
and cCustomerGUID='1f904053-06dc-494b-afed-86dfe1fd2747'
end
--提交事物
Commit TRANSACTION
--回滚事物
Rollback TRANSACTION
注意事项:decimal(32,6): 32为长度,6为保留小数位数
目的是通过A表的某两条符合条件的数据的a字段并计算这两个值的和去更新至B表的b字段中
a,b中有一些公有字段,如cCustomerGUID
因为cCustomerGUID+cName是唯一就可以根据B表的cCustomerGUID将这个字段更新至临时表,其他值就设置为任意字段同类型值(反正后面要更新)
然后就是根据条件将A表符合条件的数据匹配ID更新b,c两个字段
再更新临时表d=b+c
然后将临时表的计算好的值根据ID相等更新至B表