进销存中库存的加权平均值(150分)

用存储过程，字段名改成你自己的就可以用了<br><br>CREATE PROCEDURE GetPrice AS<br>declare @st_no varchar(10)<br>declare @st_count int<br>declare @in_count int<br>declare @in_price dec(18, 2)<br>declare @total_count int<br>declare @flag bit<br>declare @total_price dec(18, 2)<br><br>select 编码, 库存, cast(0 as dec(18, 2)) 平均进价 &nbsp;into #tempPrice from 库存表 where 库存 &gt; 0 <br><br>declare stock_cursor cursor for<br>select 编码, 库存 from #tt<br><br>open stock_cursor<br>fetch next from stock_cursor into @st_no, @st_count<br><br>while @@fetch_status = 0<br>begin<br>&nbsp; &nbsp; set @total_count = 0<br>&nbsp; &nbsp; set @flag = 0<br>&nbsp; &nbsp; set @total_price = 0<br><br>&nbsp; &nbsp; declare count_cursor &nbsp;cursor for<br>&nbsp; &nbsp; select 数量, 单价<br>&nbsp; &nbsp; from 进货表<br>&nbsp; &nbsp; where 编码 = @st_no<br>&nbsp; &nbsp; order by 时间 desc<br><br>&nbsp; &nbsp; open count_cursor<br>&nbsp; &nbsp; fetch next from count_cursor into @in_count, @in_price<br>&nbsp; &nbsp; while @@fetch_status = 0 and @flag = 0<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; set @total_count = @total_count + @in_count<br>&nbsp; &nbsp; &nbsp; &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; if @total_count &gt;= @st_count<br>&nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @flag = 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @total_price = @total_price + (@in_count - (@total_count - @st_count))*@in_price<br>&nbsp; &nbsp; &nbsp; &nbsp; end<br>&nbsp; &nbsp; &nbsp; &nbsp; else<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set @total_price = @total_price + @in_count*@in_price<br>&nbsp; &nbsp; &nbsp; &nbsp; fetch next from count_cursor into @in_count, @in_price<br>&nbsp; &nbsp; end<br>&nbsp; &nbsp; update #tempPrice set 平均进价 = @total_price/@st_count where 编码 = @st_no<br><br>&nbsp; &nbsp; close count_cursor<br>&nbsp; &nbsp; deallocate count_cursor<br><br>&nbsp; &nbsp; fetch next from stock_cursor into @st_no, @st_count<br>end<br>close stock_cursor<br>deallocate stock_cursor<br><br>select * from #tempPrice<br><br>drop table #tempPrice<br>GO

加权平均核算方式：<br>　　任何时候，平均价都等于库存总金额／总数量。<br>先进货２个，后又进货１０个，现在库存１０个。<br>如果先进货２个，卖２个，然后又进１０个，这里平均价是１６元。<br>如果先负库存销售２个，此时的成本价只能走销售价（反正我们公司的软件是这么处理的。）。这之后的情况　就比较复杂了。会出现库存数量为０，金额不为０的情况。

说一下我的看法<br><br>成本核算一般分三种类型：标准成本、实际成本、预测成本<br>加权平均价属于实际成本<br>而加权平均又可分为加权平均、移动加权平均<br>加权平均：没有计算机帮助时，一般按会计期间计算。加权平均价=(本期发生金额+上期期末金额)/(本期发生数量+上期期末数量)<br>移动加权平均：即采用计算机帮助时，对库存物料每一次发生变化都进行计算。<br><br>因此，一般来说，计算成本是系统应用底层平台的基础工作，所以应有统一的机制满足系统其它功能模块的成本计算请求。一般的做法：<br>1. 设置成本类或对象，对它请求，如销售a物料多少钱，对象自动完成本计算，并持久化在数据库中；<br>2. 设置业务表，如进出明细数据表，由它的触发器去持久化它；<br><br>以上，希望对你有所帮助。<br>如果，你需要采用你现在的方法进行计算，可以参照楼上各位的回答。