利用 SUM OVER 开窗函数实现累加计算

今天在坛子里发现一个很有意思的贴子： 点击打开链接

如上图所示，【需求数量】不变，【已满足数量】按照从序号大到小的顺序，从最小的【序号】中取值，使其等于【需求数量】
举个例子：比如物料号为1的，有三条记录：分别是序号1、3、4，序号4的【已满足数量】比【需求数量】少了1，则从序号1【已满足数量】中取出1 给序号4，序号1的【已满足数量】变为11，序号4的【已满足数量】变为5；
2、序号3的【已满足数量】比【需求数量】少了6，则从序号1【已满足数量】中取出6 给序号3，序号1的【已满足数量】变为5，序号3的【已满足数量】变为30；
3、按照这个规则一直循环下去，直到序号大的物料的【已满足数量】全部等于【需求数量】。
结果如下图所示

我的答案如下（注：必须SQL Server2012+ 才能使用）：

USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
CREATE TABLE test([序号] INT primary key, [物料号] int,[需求数量] int,[已满足数量] int)
INSERT INTO test([序号],[物料号],[需求数量],[已满足数量])
SELECT  1,1,20,12
union SELECT  2,2,26,7
union SELECT  3,1,30,24
union SELECT  4,1,5,4
union SELECT  5,2,4,4
union SELECT  6,2,9,5
--union SELECT  7,2,9,1

;with cte as (
SELECT *
,SUM([已满足数量]) OVER(PARTITION BY [物料号]) as okSum --
,SUM([需求数量]) OVER(PARTITION BY [物料号] order BY [序号] desc) as needSum2
FROM test 
),cte2 as (
	select ROW_NUMBER() over(partition by [物料号] order by [序号] desc) as rid,* from cte
),cte3 as(
select * 
,CASE when okSum>=needSum2 then [需求数量] 
else okSum-(select needSum2 from cte2 as b where a.[物料号]=b.[物料号] and b.rid=a.rid-1) end as [已满足数量2] 
from cte2 as a
)
select *,case when [已满足数量2]>0 then [已满足数量2]  else 0 end as [已满足数量3] 
from cte3

--完全按题目的数据来的执行结果， 已满足数量3 即为所求：


--增加了一行数据 序号为7 的， 主要是测试在数量不够分配时的情况结果是否正常：

msdn 参考链接： 点击打开链接