SQL语句优化
请教一下,这个更新语句,A字段是很复杂的取值逻辑,而B字段是更新后的A值加到100,为了不影响SQL的执行效率,
我要怎么处理包装下这串复杂的公式,然后B直接去引用,而不是写成B=100+原来A的取值语句
update PPBOMEntry set
A=
case when (g.fname like 'MU%) then (
case when a.fqty<=100000 then isnull(e.FEntrySelfZ0144,0)
when a.fqty>100000 and a.FQty<=200000 then 2*isnull(e.FEntrySelfZ0144,0)
else 3*isnull(e.FEntrySelfZ0144,0) end )
when g.fname='架%' then (
case when a.fqty<=40000 then isnull(e.FEntrySelfZ0144,0)
when a.fqty>40000 and a.FQty<=80000 then 2*isnull(e.FEntrySelfZ0144,0)
when a.fqty>80000 and a.FQty<=120000 then 3*isnull(e.FEntrySelfZ0144,0)
when a.fqty>120000 and a.FQty<=160000 then 4*isnull(e.FEntrySelfZ0144,0)
else 5*isnull(e.FEntrySelfZ0144,0) end )
else isnull(e.FEntrySelfZ0144,0) end
,
B=100+A(错误的,取到的值是原来A的值)
from ICMO a
left join PPBOM b on a.FInterid=b.Ficmointerid
left join PPBOMEntry c on b.FinterId=c.FinterId
left join ICBOM d on a.FBomInterId = d.FInterId
left join ICBOMChild e on d.FInterId = e.FInterId
left join t_ICItem f on c.FItemID=f.FItemID
left join t_ICItem g on a.FItemID=g.FItemID