求“先进先出法”计算销售成本价的SQL语句，并请tcmakebest来领分

[NongCunGongLu]

首先向老师们问好，给坛子里的IT人们拜年。这个贴子100分，其中有60分是给坛友tcmakebest的
原因在这个贴子：http://bbs.csdn.net/topics/390699112
我仔细一看，发现他写的SQL语句既易懂、又巧妙，朴实无华，很好用，扩充条件也方便。
但是当初结贴太着急，没给tcmakebest一个合理的分数(他说不需要，但我心里觉得放不下)
所以，发这个贴的同时给tcmakebest补分

另外40分是给解决以下问题的老师的。如觉得少....好商量！^_^

如tcmakebest来领分时，顺便把下面问题解决了，那就更好！喜欢你写的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　　 (790)　　 (70)
8 -1　　　　999　　 (790)　　 (90)

-------字段的说明------------------
RecId是主键，标识业务的发生顺序
InOrOut 1=入库；-1=出库
spId是商品ID
Dj=单价(入库时为进价，出库时为售价)
Sl=数量

---------需求----------------------
现在希望得到：按先进先出法，某时间段内(假设取RecId值4-7之间)售出商品的采购成本。
也就是说：表中，RecId在[4..7]之间的销售记录有2条：
RecId=4的那笔售出50件＝10件(590/件)＋40件(580/件)
于是，这50件成本＝590*10＋580*40＝29100
RecId=7的那笔售出70件＝40件(580/件)＋30件(570/件)
于是，这70件成本＝580*40＋570*30＝40300
因此，此时间段内的采购成本是29100＋40300＝69400
目的就是希望得到这个数字

--------注意事项-------------------
1)售出Dj可以无视它
2)为了描述直观，表中只使用了一种ID为999的商品，事实上是有多种商品
3)也可以写一个返回以下记录集的SQL查询语句(多了一列“成本价”)
4)总之，是相当的感谢了，谢谢老师们

RecId　　InOrOut spId Dj Sl　　“NewColumn-Cbj”
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=================
1　　　　　1　　　　999　　　590　　　120　　　　　 0
2　　　　 -1　　　　999　　 (800)　　(110)　　　64900　(＝590*110)
3　　　　　1　　　　999　　　580　　 　80　　　　　 0
4　　　　 -1　　　　999　　 (800)　　 (50)　　　29100　(＝590*10＋580*40)
5　　　　　1　　　　999　　　570　　　100　　　　　 0
6　　　　　1　　　　999　　　560　　 　50　　　　 　0
7　　　　 -1　　　　999　　 (790)　　 (70)　　　40300　(＝580*40＋570*30)
8 -1　　　　999　　 (790)　　 (90)　　　51100　(＝570*70＋560*20)

[KeepSayingNo]

你的需求每次都好复杂啊，我觉得你最好搞个作业对原始数据进行预处理，感觉你要的结果数据是经常需要的，对于这种经常需要的应该用预处理，后续直接查就OK了

[tcmakebest]

我是被楼主盯上了吗，不知道记录多了会不会执行的时候慢，下面就是结果了：

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,70),(8,-1,999,790,90)
;with tb_total as 
(
	select recid,dj,ioorout,spid,
	(select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid)-sl from_num,
	(select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid) to_num
	from @t t1
)
select t1.recid,t1.spid,
	SUM(t2.dj * (
		case when t1.to_num<t2.to_num then t1.to_num else t2.to_num end -
		case when t1.from_num>t2.from_num then t1.from_num else t2.from_num end
	)) Cost 
from tb_total t1
join tb_total t2 on t1.ioorout=-1 and t2.ioorout=1 and t1.spid=t2.spid and t1.from_num<t2.to_num and t1.to_num>t2.from_num
where t1.recid between 4 and 7
group by t1.recid,t1.spid

recid spid Cost ----------- ----------- ----------- 4 999 29100 7 999 40300

[qiangorqiang]

可以考虑每笔入库记录剩余数量，出库的时候进行扣减。这样计算效率会高一些。

[tenhilltree]

[xtt22cn]

高手啊 谢谢，正想要此解答