求计算平均价的SQL语句

Question

由于之前计算算法的错误，现有数据的平均价已经全部错乱。
现在准备将通过所有进货记录来重新计算每个商品的进货价。商品表 
编号 商品名称  平均进货价进货表
编号 商品代码 进货数量 进货总价 进货单价
销售表 
编号 商品代码 销售数量 销售总价 销售单价  成本价现在要求，通过进货记录里面 的数据重新计算出每种商品的平均进货价 （该商品进货总价之和 除以  该商品进货数量之和 ） 然后将这个平均进货价更新到商品表中的平均进货价 以及 销售表中的成本价字段中。不知道该SQL语句应该如何组织，请高人指点。

Answer 1

写个函数吧，先把数据列定义下。
例如
编号 商品名称 平均进货价  avg(平均进货价)

Answer 2

declare @进货表 table 
(编号 int,商品代码 varchar(3),进货数量 int,进货总价 int,进货单价 int)
insert into @进货表
select 1,'011',13,65,5 union all
select 2,'011',40,280,7 union all
select 3,'013',100,800,8 union all
select 4,'013',200,700,7select 商品代码,round(cast(sum(进货总价) as float)/sum(进货数量),2) as 平均价格 
from @进货表 group by 商品代码
/*
商品代码 平均价格
---- ----------------------
011  6.51
013  5
*/

Answer 3

;with maco as 
(
select 商品代码,round(cast(sum(进货总价) as float)/sum(进货数量),2) as 平均价格 
from @进货表 group by 商品代码
)
--更新到商品表(商品表)
update 商品表
set 平均进货价=b.平均价格  
编号 商品代码 平均进货价
from 商品表 a left join maco b on a.商品代码=b.商品代码

Answer 4

商品表中竟然没有商品代码？
#6代码中间多了一行“编号 商品代码 平均进货价” ，修正！
;with maco1 as 
(
select 商品代码,round(cast(sum(进货总价) as float)/sum(进货数量),2) as 平均价格 
from @进货表 group by 商品代码
)
--更新到销售表(商品表)
update 销售表
set 成本价=b.平均价格  
from 销售表 a left join maco1 b on a.商品代码=b.商品代码;with maco2 as 
(
select 商品代码,round(cast(sum(进货总价) as float)/sum(进货数量),2) as 平均价格 
from @进货表 group by 商品代码
)
--更新到商品表(商品表)
update 商品表
set 平均进货价=b.平均价格  
from 商品表 a left join maco2 b on a.商品代码=b.商品代码

Answer 5

我的数据库是SQL SERVER 2000
好像; with as 功能只有2005里面才有

Answer 6

要用两条SQL语句吧..1.更新商品表的平均进货价,商品表应该还有一个商品代码的字段吧update 商品表 SET 商品表.平均进货价=tablea.AvgPrice FROM 商品表 
       INNER JOIN (select 商品代码,SUM(进货总价)/SUM(进货数量) AS AvgPrice FROM 进货表 GROUP BY 商品代码) AS tablea
       ON 商品表.商品代码=tablea.商品代码2.更新销售表的成本价,商品表应该还有一个商品代码的字段吧update 销售表 SET 销售表.成本价=tablea.AvgPrice FROM 销售表 
       INNER JOIN (select 商品代码,SUM(进货总价)/SUM(进货数量) AS AvgPrice FROM 进货表 GROUP BY 商品代码) AS tablea
       ON 销售表.商品代码=tablea.商品代码

Answer 7

1. 通过进货记录里面的数据重新计算出每种商品的平均进货价
select sum(进货总价)/sum(进货数量) as 重新计算平均价格 from 进货表 group by 商品代码 having 日期>=指定日期
2. 平均进货价更新到商品表中的平均进货价
update 商品表 set 平均进货价=重新计算平均价格 from 商品表 left join 进货表 on 商品表.商品代码=进货表.商品代码
3. 平均进货价更新到销售表中的成本价字段
update 销售表 set 成本价=重新计算平均价格 from 销售表 left join 商品表 on 销售表.商品代码=商品表.商品代码 and 销售表.日期>=指定日期