SQL Server如何按总货量与单箱上限更新订单项装箱数量

核心思路是通过窗口函数计算每个lineItem的累计最大可装量，再结合总货量动态计算每行应填充的数值，逻辑如下：

• 按saleId分组、lineItem升序排序，计算到当前行为止所有行的maxAllowedInBox累计和
• 每行的可装值取「累计和与总货量的较小值」减去上一行的累计和（第一行则减去0），自动实现靠前行装到单箱上限、最后一行装剩余值的需求，同时兼容总货量不足以装满所有靠前行的边界场景

WITH LineItemCumulative AS (
    SELECT 
        *,
        -- 计算到当前行的累计最大可装量
        SUM(maxAllowedInBox) OVER (PARTITION BY saleId ORDER BY lineItem) AS cum_max,
        -- 计算上一行的累计最大可装量，首行默认补0
        ISNULL(LAG(SUM(maxAllowedInBox) OVER (PARTITION BY saleId ORDER BY lineItem)) OVER (PARTITION BY saleId ORDER BY lineItem), 0) AS prev_cum_max
    FROM Testing
)
UPDATE LineItemCumulative
SET itemsInBox = CASE 
    -- 累计未超过总货量，装到单箱上限
    WHEN cum_max <= totalQty THEN maxAllowedInBox
    -- 累计超过总货量但仍有剩余，装剩余部分
    WHEN totalQty - prev_cum_max > 0 THEN totalQty - prev_cum_max
    -- 无剩余货量，填0
    ELSE 0
END

执行更新后查询saleId=123的测试数据，itemsInBox字段值分别为21、21、8，三者总和等于总货量50，完全符合需求。该逻辑同时兼容同个saleId下不同lineItem的maxAllowedInBox数值不一致的场景，扩展性更强。

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