今天在坛子里发现一个很有意思的贴子: 点击打开链接
如上图所示,【需求数量】不变,【已满足数量】按照从序号大到小的顺序,从最小的【序号】中取值,使其等于【需求数量】
举个例子:比如物料号为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 参考链接: 点击打开链接