select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码现在要把上面算出来的jhdj替换到 物资库 中,代码如下,不太会写了,请大家指教:
update 物资库
set 计划单价= jq.jhdj
where jq.jhdj in ((select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) jq)
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码现在要把上面算出来的jhdj替换到 物资库 中,代码如下,不太会写了,请大家指教:
update 物资库
set 计划单价= jq.jhdj
where jq.jhdj in ((select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) jq)
update 物资库
set 计划单价=select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码
set 计划单价=select
kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码
update 物资库
set 计划单价=t.jhdj
from 物资库 m,
(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) t
on
a.物资编码 = b.物资编码
)t
where t.物资编码 =m.物资编码
update a set a.计划单价=c.jhdj from 物资库 a,(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) jq)) c where a.物资编码=b.我将编码
update 物资库
set 计划单价=ab.jhdj
from 物资库,(select
b.物资编码,(kje+yje)/(kcsl+rks) as jhdj
from
(select 物资编码,库存数量 as kcsl,库存数量*计划单价 as kje from 物资库) a
join
(select 物资编码 ,sum(入库数) as rks ,sum(入库数*实际单价) as yje from 验收 group by 物资编码) b
on
a.物资编码 = b.物资编码) as ab where 物资库.物资编码 = ab.物资编码