if OBJECT_ID('tb') is not null drop table tb
if OBJECT_ID('TEMP') is not null drop table TEMP
if OBJECT_ID('FUN_NOWPRICE') is not null drop FUNCTION FUN_NOWPRICE
if OBJECT_ID('FUN_NOWQTY') is not null drop FUNCTION FUN_NOWQTY
go

create table tb(
id INT
,Date1 datetime
,ctype varchar(10)
,qnt float
,pri float
)

--qnt 数量
--pri 单价
insert tb
select 0,'2009-1-1', '进货',  10,  100 union all
select 1,'2009-1-139

alter table tb add newc int;
alter table tb add nowpric numeric(18,12)
go
 
update b  
    set newc =isnull((select sum( case when ctype= '进货' then qnt else -qnt end)
                        from tb
                        where id <=b.id),0)
from tb b 
 
declare @d numeric(18,12)
set @d = 0 
 
update tb
    set nowpric = @d*1.0/newc, 
        @d = @d + case when ctype = '进货' then qnt else - qnt end * 
        case when ctype = '进货' then pri else @d*1.0 / (newc+qnt) end

select * from tb

------解决方案--------------------

SQL code

create function getP1(@id int) returns real
as 
begin
declare @P real 
declare @type nvarchar(20) 
declare @price real
declare @num real
select @price=pri,@num=qnt,@type= ctype  from tb where id=@id
set @P=case when @id=0 then @price 
            else case when @type='进货' then (dbo.getN(@id)*dbo.getP(@id-1)+@price*@num)/(dbo.getN(@id)+@num)
                 else dbo.getP(@id-1)
                 end
       end
return @P
end

create function getN(@id int) returns real
as
begin
   declare @lastnum real  declare @lnum real
   select @lastnum=isnull(sum(qnt),0) from tb  where id<@id and ctype='进货' 
   select @lnum=isnull(sum(qnt),0) from tb where id<@id and ctype='出货'
return @lastnum-@lnum
end

select c.*,dbo.getP1(id) AVGprice from tb as c

------解决方案--------------------

都是高人！
以前做过类似的东东。我的做法是在产品表中建立出库价格字段，每次更新的。出库的时候价格不变，入库的时候根据当时的出库价格计算出的价格更新出库价格。