求“先进先出法”计算库存商品余额价值的SQL语句

NongCunGongLu 2014-01-22 06:11:43

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的情况）。
顺祝论坛朋友们，各位光临本贴的老师们，马年顺利！

...全文

518 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

NongCunGongLu 2014-01-24

打赏
举报

引用 12 楼 tcmakebest 的回复:

写的SQL有人能看懂并得到好评我就很高兴了，补分就不必了，谢谢你的诚意，呵！加油

本想学着你的方法,再解决一个类似的问题,但是我失败了. 对sql了解得太少,一直不入门.. 劳驾去拿分吧, 如果能顺便帮我解决一下问题, 就太感谢了. 请点击:http://bbs.csdn.net/topics/390699977

tcmakebest 2014-01-23

打赏
举报

引用 11 楼 NongCunGongLu 的回复:

[quote=引用 10 楼 tcmakebest 的回复:] 这个结果是90800：......

tcmakebest老师，你好！当初结贴太着急，现在发现你的SQL语句写得也是相当有思想，很巧妙。敬佩！因为贴子已经结了，打算这几天重新开贴给你补分，在此先向你致谢了！[/quote] 写的SQL有人能看懂并得到好评我就很高兴了，补分就不必了，谢谢你的诚意，呵！加油

NongCunGongLu 2014-01-23

打赏
举报

引用 10 楼 tcmakebest 的回复:

这个结果是90800：......

tcmakebest老师，你好！当初结贴太着急，现在发现你的SQL语句写得也是相当有思想，很巧妙。敬佩！因为贴子已经结了，打算这几天重新开贴给你补分，在此先向你致谢了！

tcmakebest 2014-01-23

打赏
举报

这个结果是90800：

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

LongRui888 2014-01-22

打赏
举报

引用 8 楼 NongCunGongLu 的回复:

2005的,老师最好是SQL语句,不要存储过程

呵呵，写了一个，你试试：

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
/*
spId	sl
999	90800
*/

NongCunGongLu 2014-01-22

打赏
举报

2005的,老师最好是SQL语句,不要存储过程

LongRui888 2014-01-22

打赏
举报

引用 6 楼 NongCunGongLu 的回复:

首先感谢楼上几位老师! 我的表达不准确，纠正一下：出库时的价格，是销售价，你们完全可以无视它。出库只影响库存的现存量。也就是说： RecId InOrOut spId Dj Sl ------------------------------------------ 1 1 999 590 120 此时，库存999商品120件，价值590x120=70800 2 -1 999 (800) (110)此时，库存999商品 10件，价值590x10=5900 3 1 999 580 80 此时，库存999商品 90件，价值5900+580x80=52300 4 -1 999 (800) (50)此时，库存999商品 40件，价值580x40=23200 5 1 999 570 100 此时，库存999商品140件，价值23200+570x100 6 1 999 560 50 此时，库存999商品190件，价值23200+570x100+560x50 7 -1 999 (800) (30)此时，库存999商品160件，价值580x10+570x100+560x50 = 90800 其实最终要的就是这个90800 [quote=引用 2 楼 yupeigu 的回复:] 按照你的需求，查询的结果是什么呢，能贴出来不

yupeigu老师，其实我要的就是一个值：“库中商品现存价值”。按先进先出计算。请老师再看一下...多谢！[/quote] 是2000，还是2005的

NongCunGongLu 2014-01-22

打赏
举报

首先感谢楼上几位老师! 我的表达不准确，纠正一下：出库时的价格，是销售价，你们完全可以无视它。出库只影响库存的现存量。也就是说： RecId InOrOut spId Dj Sl ------------------------------------------ 1 1 999 590 120 此时，库存999商品120件，价值590x120=70800 2 -1 999 (800) (110)此时，库存999商品 10件，价值590x10=5900 3 1 999 580 80 此时，库存999商品 90件，价值5900+580x80=52300 4 -1 999 (800) (50)此时，库存999商品 40件，价值580x40=23200 5 1 999 570 100 此时，库存999商品140件，价值23200+570x100 6 1 999 560 50 此时，库存999商品190件，价值23200+570x100+560x50 7 -1 999 (800) (30)此时，库存999商品160件，价值580x10+570x100+560x50 = 90800 其实最终要的就是这个90800

引用 2 楼 yupeigu 的回复:

按照你的需求，查询的结果是什么呢，能贴出来不

yupeigu老师，其实我要的就是一个值：“库中商品现存价值”。按先进先出计算。请老师再看一下...多谢！

LongRui888 2014-01-22

打赏
举报

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

披着虎皮的石头 2014-01-22

打赏
举报

--加减法，更新明细账的结余数量和金额 @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