如何实现符合分包商规则的SQL列累计求和（非动态查询）

需求规则 #

• 累计求和当前行之前的库存
• 当前行库存若位于该工序的分包商处，不纳入求和范围
• 排除所有处于分包商层级的库存
• 示例参考：Operation_No为40时Summation结果为4132，Operation_No为60时为13732

现有代码（仅满足规则1） #

SELECT
    *, 
    SUM(Available_Inventory) OVER (PARTITION BY Part_No, Job_No 
                                   ORDER BY Operation_No) AS Summation 
FROM #Inventory_Sum

优化后的解决方案 #

针对三个规则，通过在窗口函数中加入条件判断并调整求和范围实现需求：

SELECT
    *,
    -- 仅对非分包商的库存求和，且只包含当前行之前的记录
    SUM(CASE 
          WHEN Subcontractor = 'N' THEN Available_Inventory 
          ELSE 0 
        END) OVER (
          PARTITION BY Part_No, Job_No 
          ORDER BY Operation_No 
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS Summation
FROM #Inventory_Sum

方案说明 #

1. ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：限定求和范围为当前行之前的所有历史行，完全满足规则1中"累计当前行之前库存"的要求
2. CASE条件判断：将分包商层级的库存值置为0，确保这类库存不被计入求和结果，同时排除当前行若为分包商时的库存，满足规则2和3

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