如何优化无日期列的SQL期间求和？获取YTD预算数据的更佳方案

问题分析 #

原查询通过冗长的CASE语句按期间累加YTD预算，不仅代码冗余，后续维护（比如新增期间）也很麻烦，且存在不必要的自连接操作。以下是两种更简洁高效的实现方式：

方案一：条件直接求和（无需表转置） #

利用CASE WHEN对每个期间列进行判断，仅累加ThisMonth之前的预算，代码更紧凑：

DECLARE @ThisMonth INT = 4; -- 传入的月份参数

SELECT
    -- 直接对每个期间列判断，符合条件则累加
    SUM(
        CASE WHEN 1 <= @ThisMonth THEN BudgetAmtPeriod1 ELSE 0 END +
        CASE WHEN 2 <= @ThisMonth THEN BudgetAmtPeriod2 ELSE 0 END +
        CASE WHEN 3 <= @ThisMonth THEN BudgetAmtPeriod3 ELSE 0 END +
        CASE WHEN 4 <= @ThisMonth THEN BudgetAmtPeriod4 ELSE 0 END +
        CASE WHEN 5 <= @ThisMonth THEN BudgetAmtPeriod5 ELSE 0 END +
        CASE WHEN 6 <= @ThisMonth THEN BudgetAmtPeriod6 ELSE 0 END +
        CASE WHEN 7 <= @ThisMonth THEN BudgetAmtPeriod7 ELSE 0 END +
        CASE WHEN 8 <= @ThisMonth THEN BudgetAmtPeriod8 ELSE 0 END +
        CASE WHEN 9 <= @ThisMonth THEN BudgetAmtPeriod9 ELSE 0 END +
        CASE WHEN 10 <= @ThisMonth THEN BudgetAmtPeriod10 ELSE 0 END +
        CASE WHEN 11 <= @ThisMonth THEN BudgetAmtPeriod11 ELSE 0 END +
        CASE WHEN 12 <= @ThisMonth THEN BudgetAmtPeriod12 ELSE 0 END
    ) AS [LaborCOGS-CO],
    '0' AS [LaborCOGS-AO],
    '0' AS IndirectLabor,
    '0' AS MiscExpense,
    '0' AS LabelCost,
    '0' AS BuildMaint,
    '0' AS ShipCost,
    '0' AS RDR,
    '0' AS NetSales
FROM GLAccountBudgetDetails d
JOIN GLAccountBudget b WITH(NOLOCK) 
    ON d.GLAccountBudgetID = b.GLAccountBudgetID
WHERE d.GLAccountID IN(256,257,258,266) 
  AND b.FiscalYear = 2024;

优化点：

• 移除了冗余的自连接，直接使用原表列计算
• 代码结构清晰，新增期间只需添加对应CASE行即可

方案二：UNPIVOT转置为行存储（更灵活） #

将列存储的期间预算转置为行，再通过筛选期间范围求和，适合需要频繁调整月份或扩展期间的场景：

DECLARE @ThisMonth INT = 4; -- 传入的月份参数

SELECT
    SUM(PeriodBudget) AS [LaborCOGS-CO],
    '0' AS [LaborCOGS-AO],
    '0' AS IndirectLabor,
    '0' AS MiscExpense,
    '0' AS LabelCost,
    '0' AS BuildMaint,
    '0' AS ShipCost,
    '0' AS RDR,
    '0' AS NetSales
FROM (
    -- 将期间列转置为（期间编号，预算金额）的行
    SELECT
        d.GLAccountBudgetDetailID,
        CAST(RIGHT(col, 2) AS INT) AS PeriodNum, -- 提取期间编号
        colValue AS PeriodBudget
    FROM GLAccountBudgetDetails d
    CROSS APPLY (
        VALUES
            ('BudgetAmtPeriod1', BudgetAmtPeriod1),
            ('BudgetAmtPeriod2', BudgetAmtPeriod2),
            ('BudgetAmtPeriod3', BudgetAmtPeriod3),
            ('BudgetAmtPeriod4', BudgetAmtPeriod4),
            ('BudgetAmtPeriod5', BudgetAmtPeriod5),
            ('BudgetAmtPeriod6', BudgetAmtPeriod6),
            ('BudgetAmtPeriod7', BudgetAmtPeriod7),
            ('BudgetAmtPeriod8', BudgetAmtPeriod8),
            ('BudgetAmtPeriod9', BudgetAmtPeriod9),
            ('BudgetAmtPeriod10', BudgetAmtPeriod10),
            ('BudgetAmtPeriod11', BudgetAmtPeriod11),
            ('BudgetAmtPeriod12', BudgetAmtPeriod12)
    ) AS unpvt(col, colValue)
    JOIN GLAccountBudget b WITH(NOLOCK) 
        ON d.GLAccountBudgetID = b.GLAccountBudgetID
    WHERE d.GLAccountID IN(256,257,258,266) 
      AND b.FiscalYear = 2024
) AS Periods
WHERE PeriodNum <= @ThisMonth; -- 筛选YTD范围内的期间

优化点：

• 转置后逻辑更直观，支持动态调整月份范围
• 若后续新增BudgetAmtPeriod13等列，只需在VALUES中添加对应行即可
• 避免了多个SUM嵌套的复杂计算

额外优化提示 #

• 原查询中的LEFT JOIN可改为INNER JOIN，因为WHERE条件中依赖GLAccountBudget.FiscalYear，左连接会被自动转为内连接，显式使用内连接更清晰
• 若GLAccountBudgetID和GLAccountID存在索引，查询性能会进一步提升

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