求“先进先出法”计算库存商品余额价值的SQL语句
MS-SQL数据库，表Tb的结构和内容如下
RecId InOrOut spId Dj Sl
------------------------------------------
1 1 999 590 120
2 -1 999 (800) (110)
3 1 999 580 80
4 -1 999 (800) (50)
5 1 999 570 100
6 1 999 560 50
7 -1 999 (800) (30)
上述记录是按RecId顺序发生的
其中InOrOut为1时，表明商品入库；InOrOut为-1时，表明商品出库
Dj和Sl分别是当时入库或出库时的单价、数量
spId是不同商品的ID，为了描述直观，表中只列出一种商品的情况
显然，在第7条记录（销售出库30件）完成时，spId为999的商品剩余160件，这160件商品的价值，按先进先出法，应为90800元，即：
第6条记录的(50*560=28000) + 第5条记录的(100*570=57000) + 第3条记录的(10*580=5800)，合计为90800元
求计算表Tb中现余商品的价值的SQL语句，多谢（要考虑多种商品spId的情况）。
顺祝论坛朋友们，各位光临本贴的老师们，马年顺利！
[解决办法]
做移动平均加权，简单很多。
[解决办法]
按照你的需求，查询的结果是什么呢，能贴出来不
[解决办法]
select
spid,
SUM(SI*InOrOut) AS SL,
SUM(DJ*SI*InOrOut) AS Value
from tb
group by spid
[解决办法]
--加减法，更新明细账的结余数量和金额 @sBeginQty和@sBeginSum期初数量和金额,单价＝金额/数量。
Create Table #ResStoreAccDet_Sums
(
GID varchar(50) null,
BeginQty Numeric(18, 6) null,
BeginSum Numeric(18, 6) null,
InQty Numeric(18, 6) null,
InSum Numeric(18, 6) null,
OutQty Numeric(18, 6) null,
OutSum Numeric(18, 6) null,
EndQty Numeric(18, 6) null,
EndSum Numeric(18, 6) null)
update #ResStoreAccDet_Sums
set EndQty =(select @sBeginQty +Sum(Isnull(InQty, 0)) -Sum(Isnull(OutQty, 0))
from #ResStoreAccDet_Sums where SID <=t.SID),
EndSum =(select @sBeginSum +Sum(Isnull(InSum, 0)) -Sum(Isnull(OutSum, 0))
from #ResStoreAccDet_Sums where SID <=t.SID)
from #ResStoreAccDet_Sums t
if object_id('[tb]') is not null drop table tb
go
create table tb(RecId int, InOrOut int, spId int,Dj int,Sl int)
insert into tb
select 1 , 1 ,999 ,590 ,120 union all
select 2 , -1 , 999 ,800 ,110 union all
select 3 , 1 , 999 ,580 ,80 union all
select 4 , -1 , 999 ,800 ,50 union all
select 5 , 1 , 999 ,570 ,100 union all
select 6 , 1 , 999 ,560 ,50 union all
select 7 , -1 , 999 ,800 ,30
go
select RecId,
InOrOut,
spId,
InOrOut*dj*sl,
(select SUM(InOrOut*Dj*Sl) from tb t2 where t1.spid = t2.spId and t2.RecId<=t1.RecId)
from tb t1
if object_id('[tb]') is not null drop table tb
go
create table tb(RecId int, InOrOut int, spId int,Dj int,Sl int)
insert into tb
select 1 , 1 ,999 ,590 ,120 union all
select 2 , -1 , 999 ,800 ,110 union all
select 3 , 1 , 999 ,580 ,80 union all
select 4 , -1 , 999 ,800 ,50 union all
select 5 , 1 , 999 ,570 ,100 union all
select 6 , 1 , 999 ,560 ,50 union all
select 7 , -1 , 999 ,800 ,30
go
select spId, sum(xx) as sl
from
(
select t1.spId,
--sum_sl - t2.sl as diff,
case when t1.recid = min(t1.RecId) over(partition by t1.spid)
then Dj * (sum_sl - isnull(t2.sl,0))
else Dj*t1.Sl
end xx
from
(
select RecId,
spId,
Dj,
sl,
(select SUM(sl) from tb t2 where t1.spId = t2.spId
and t1.RecId>= t2.RecId and InOrOut = 1) as sum_sl
from tb t1
where InOrOut = 1
)t1
left join
(
select spid,sum(sl) sl
from tb
where InOrOut = -1
group by spId
)t2
on t1.spId = t2.spId
where sum_sl - isnull(t2.sl,0) > 0
)t
group by spid
/*
spIdsl
99990800
*/
declare @t table ( recid int, ioorout int,spid int,dj int,sl int )
insert into @t values (1,1,999,590,120),(2,-1,999,800,110),(3,1,999,580,80)
,(4,-1,999,800,50),(5,1,999,570,100),(6,1,999,560,50),(7,-1,999,800,30)
select sum(case when kucun+sl-subtotal>sl then sl else kucun+sl-subtotal end * dj) from
(select sl,dj,
(select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=1 and sub.recid>=t1.recid) subtotal,
(select SUM(ioorout*sl) from @t sub2 where sub2.spid=t1.spid) kucun
from @t t1
where ioorout=1
) tmp
where kucun+sl-subtotal>0