如何编写T-SQL查询按ID1及Rank顺序分配数量至Reference值

示例数据 #

需求说明 #

按ID1分组，以Rank为优先级顺序分配ToAllocate数值，每组的总分配上限为Reference值：

• 优先满足Rank靠前的记录
• 剩余可分配额度足够时，Result取ToAllocate的数值
• 剩余额度不足时，Result取剩余额度
• 额度耗尽后，后续记录Result为0

解决方案代码 #

SELECT 
    ID1,
    ID2,
    Rank,
    ToAllocate,
    Reference,
    Result = GREATEST(
        0,
        LEAST(
            ToAllocate,
            Reference - ISNULL(SUM(ToAllocate) OVER (PARTITION BY ID1 ORDER BY Rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
        )
    )
FROM 
    YourTableName -- 替换成你的实际表名
ORDER BY 
    ID1, Rank;

代码逻辑解释 #

1. 累计已分配量计算：SUM(ToAllocate) OVER (PARTITION BY ID1 ORDER BY Rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 计算当前记录之前所有已分配的总量，用ISNULL处理组内第一条记录的NULL情况（此时累计量为0）。
2. 剩余额度计算：用Reference减去累计已分配量，得到当前记录可使用的剩余额度。
3. 分配值确定：用LEAST取ToAllocate和剩余额度的较小值，确保不超过总上限；再用GREATEST确保结果不会为负数（额度耗尽时取0）。

替代写法（SQL Server 2022+） #

如果使用SQL Server 2022及以上版本，可简化累计量的计算：

SELECT 
    ID1,
    ID2,
    Rank,
    ToAllocate,
    Reference,
    Result = GREATEST(
        0,
        LEAST(
            ToAllocate,
            Reference - (SUM(ToAllocate) OVER (PARTITION BY ID1 ORDER BY Rank) - ToAllocate)
        )
    )
FROM 
    YourTableName
ORDER BY 
    ID1, Rank;

内容的提问来源于stack exchange，提问作者FornSql