如何用T-SQL按ItemCode和Facility计算累计总量？

需求说明 #

基于ItemCode和Facility分组计算累计总量（Running Total Quantity），数据来自临时表#MainQuery。

现有查询代码 #

SELECT 
    MQ.[Company], 
    MQ.[status],
    MQ.[Facility],
    MQ.[ItemCode],
    MQ.[Style],
    MQ.[Description],
    CONVERT(date, MQ.[PlannedDate], 101) AS [PlannedDate],
    MONTH(CONVERT(date, CONVERT(date, MQ.[PlannedDate], 101))) AS [PlannedDateMonth],
    YEAR(CONVERT(date, CONVERT(date, MQ.[PlannedDate], 101))) AS [PlannedDateYear],
    MQ.[On-HandApprove],
    MQ.[TransactionQuantity],
    MQ.[OrderNumber],
    MQ.[OrderLineNumber],
    MQ.[WorkOrderNumber],
    MQ.[PurchaseOrderNumber],
    MQ.[PurchaseOrderLineNumber],
    MQ.[OrderQuantity],
    MQ.[InTransit],
    MQ.[OutstandingQuantity],
    MQ.[PODueDate]
FROM 
    #MainQuery MQ
ORDER BY 
    MQ.[ItemCode], CONVERT(date, MQ.[PlannedDate], 101)

示例数据与预期结果 #

计算规则 #

• 每个ItemCode/Facility分组的第一条记录：Running Total Quantity = On-HandApprove - TransactionQuantity + OutstandingQuantity
• 同分组的后续记录：Running Total Quantity = 上一条Running Total Quantity - TransactionQuantity + OutstandingQuantity

问题分析 #

你尝试的窗口函数逻辑错误，核心是混淆了"迭代计算"和"简单累计求和"的逻辑，原写法的加减顺序和累计范围不符合规则要求。

正确解决方案 #

方案一：基于窗口求和的高效写法 #

你的计算规则可以转化为：分组初始值为On-HandApprove - TransactionQuantity + OutstandingQuantity，后续每条是累加(-TransactionQuantity + OutstandingQuantity)，因此可以用两次窗口求和实现：

SELECT 
    MQ.[Company], 
    MQ.[status],
    MQ.[Facility],
    MQ.[ItemCode],
    MQ.[Style],
    MQ.[Description],
    CONVERT(date, MQ.[PlannedDate], 101) AS [PlannedDate],
    MONTH(CONVERT(date, MQ.[PlannedDate], 101)) AS [PlannedDateMonth],
    YEAR(CONVERT(date, MQ.[PlannedDate], 101)) AS [PlannedDateYear],
    MQ.[On-HandApprove],
    MQ.[TransactionQuantity],
    MQ.[OrderNumber],
    MQ.[OrderLineNumber],
    MQ.[WorkOrderNumber],
    MQ.[PurchaseOrderNumber],
    MQ.[PurchaseOrderLineNumber],
    MQ.[OrderQuantity],
    MQ.[InTransit],
    MQ.[OutstandingQuantity],
    MQ.[PODueDate],
    -- 计算累计总量
    MQ.[On-HandApprove] 
    - SUM(MQ.[TransactionQuantity]) OVER (PARTITION BY MQ.Facility, MQ.ItemCode ORDER BY CONVERT(date, MQ.[PlannedDate], 101))
    + SUM(MQ.[OutstandingQuantity]) OVER (PARTITION BY MQ.Facility, MQ.ItemCode ORDER BY CONVERT(date, MQ.[PlannedDate], 101))
    AS [Running Total Quantity]
FROM 
    #MainQuery MQ
ORDER BY 
    MQ.[ItemCode], CONVERT(date, MQ.[PlannedDate], 101)

方案二：基于LAG函数的迭代写法 #

如果需要更直观的迭代逻辑，可以用LAG()获取上一条的累计值，结合CASE判断分组首条记录：

WITH RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Facility, ItemCode ORDER BY CONVERT(date, PlannedDate, 101)) AS RowNum
    FROM #MainQuery
)
SELECT 
    RD.[Company], 
    RD.[status],
    RD.[Facility],
    RD.[ItemCode],
    RD.[Style],
    RD.[Description],
    CONVERT(date, RD.[PlannedDate], 101) AS [PlannedDate],
    MONTH(CONVERT(date, RD.[PlannedDate], 101)) AS [PlannedDateMonth],
    YEAR(CONVERT(date, RD.[PlannedDate], 101)) AS [PlannedDateYear],
    RD.[On-HandApprove],
    RD.[TransactionQuantity],
    RD.[OrderNumber],
    RD.[OrderLineNumber],
    RD.[WorkOrderNumber],
    RD.[PurchaseOrderNumber],
    RD.[PurchaseOrderLineNumber],
    RD.[OrderQuantity],
    RD.[InTransit],
    RD.[OutstandingQuantity],
    RD.[PODueDate],
    -- 迭代计算累计值
    CASE 
        WHEN RD.RowNum = 1 THEN RD.[On-HandApprove] - RD.[TransactionQuantity] + RD.[OutstandingQuantity]
        ELSE LAG(RunningTotal) OVER (PARTITION BY RD.Facility, RD.ItemCode ORDER BY CONVERT(date, RD.[PlannedDate], 101)) 
             - RD.[TransactionQuantity] + RD.[OutstandingQuantity]
    END AS [Running Total Quantity]
FROM RankedData RD
ORDER BY 
    RD.[ItemCode], CONVERT(date, RD.[PlannedDate], 101)

两种方案都能匹配你的预期结果，方案一性能更优，适合大数据量场景。

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