bh sale zzsale
a01 1.3
a01 1.5
a01 1.8
a02 3.5
a02 3.0
。。。
T2，汇总表
bh sumsale sumzzsale
a01 4.6
a02 6.5
设有两个变量分别是@a--指定bh,@b--更新变量值是公式
假如本次更新a01,更新值是2,set公式是2/(1.3+1.5+1.8)=0.4348--这里的bh的记录条数不是固定的，有可能很多。
更新后表变更为
T1
bh sale zzsale
a01 1.3 1.3*0.4348
a01 1.5 1.5*0.4348
a01 1.8 1.8*0.4348
a02 3.5
a02 3.0
T2
bh sumsale sumzzsale
a01 4.6 （1.3+1.5+1.8）*0.4348=2.0001 --四舍五入小数点4位
a02 6.5
如何实现？
4 个解决方案
#1
update ....from ...可以实现
#2
zzsale,和sumzzsale 是字符类型的么？作为注释列？
#3
--更新T1的zzsale
update T1 set zzsale=(select T1.sale*asale from (select bh,convert(decimal(15,4),2/sum(sale)) as asale from T1
group by bh) a where a.bh=T1.bh)
--更新Ｔ2的sumzzsale
update T2 set sumzzsale=( select sumsale from(select bh, convert(decimal(15,4),sum(sale)*convert(decimal(15,4),2/sum(sale))) as sumsale from T1
group by bh) a where a.bh=T2.bh)
#4
等级不在高，有心则灵
#1
update ....from ...可以实现
#2
zzsale,和sumzzsale 是字符类型的么？作为注释列？
#3
--更新T1的zzsale
update T1 set zzsale=(select T1.sale*asale from (select bh,convert(decimal(15,4),2/sum(sale)) as asale from T1
group by bh) a where a.bh=T1.bh)
--更新Ｔ2的sumzzsale
update T2 set sumzzsale=( select sumsale from(select bh, convert(decimal(15,4),sum(sale)*convert(decimal(15,4),2/sum(sale))) as sumsale from T1
group by bh) a where a.bh=T2.bh)
#4
等级不在高，有心则灵