如何对SQL子查询返回的两行TOTAL列结果求和？

当然有办法实现这个需求！而且我们可以顺便优化你原查询里重复执行子查询的问题，让整个语句更高效。下面是几种可行的方案：

方案1：使用CTE（公共表表达式）先获取基础结果再求和 #

CTE可以先把你原查询返回的两行结果临时存储起来，然后直接对TOTAL列求和，结构清晰易读：

WITH EntitlementSummary AS (
    SELECT 
        ae.[intEntitlementID],
        SUM(decIncrease) - SUM([decDecrease]) AS quantity,
        (
            SELECT SUM([decCredit]) - SUM([decDebit]) 
            FROM [tblMonetaryValueEvent] 
            WHERE [intEntitlementID] = ae.intEntitlementID 
              AND intSchemeYear <= @intSchemeYear 
            GROUP BY intEntitlementID
        ) AS VALUE,
        (SUM(decIncrease) - SUM([decDecrease])) * (
            SELECT SUM([decCredit]) - SUM([decDebit]) 
            FROM [tblMonetaryValueEvent] 
            WHERE [intEntitlementID] = ae.intEntitlementID 
              AND intSchemeYear <= @intSchemeYear 
            GROUP BY intEntitlementID
        ) AS TOTAL
    FROM [tblAllocationEvent] ae 
    WHERE ae.intBusinessID = @intBusinessID 
      AND ae.intSchemeYear <= @intSchemeYear 
    GROUP BY ae.intEntitlementID 
    HAVING SUM(decIncrease) - SUM([decDecrease]) > 0
)
SELECT SUM(TOTAL) AS TotalSum FROM EntitlementSummary;

方案2：直接将原查询作为子查询求和 #

如果不想用CTE，也可以直接把原查询嵌套成子查询，外层直接求和：

SELECT SUM(TOTAL) AS TotalSum
FROM (
    SELECT 
        ae.[intEntitlementID],
        SUM(decIncrease) - SUM([decDecrease]) AS quantity,
        (
            SELECT SUM([decCredit]) - SUM([decDebit]) 
            FROM [tblMonetaryValueEvent] 
            WHERE [intEntitlementID] = ae.intEntitlementID 
              AND intSchemeYear <= @intSchemeYear 
            GROUP BY intEntitlementID
        ) AS VALUE,
        (SUM(decIncrease) - SUM([decDecrease])) * (
            SELECT SUM([decCredit]) - SUM([decDebit]) 
            FROM [tblMonetaryValueEvent] 
            WHERE [intEntitlementID] = ae.intEntitlementID 
              AND intSchemeYear <= @intSchemeYear 
            GROUP BY intEntitlementID
        ) AS TOTAL
    FROM [tblAllocationEvent] ae 
    WHERE ae.intBusinessID = @intBusinessID 
      AND ae.intSchemeYear <= @intSchemeYear 
    GROUP BY ae.intEntitlementID 
    HAVING SUM(decIncrease) - SUM([decDecrease]) > 0
) AS SubQuery;

方案3：优化重复子查询，提升性能 #

你原查询里两次调用了同一个子查询计算VALUE，这会导致重复计算。我们可以先提前计算每个intEntitlementID的VALUE，再通过关联查询来获取，这样性能会更好，之后再求和：

WITH MonetaryValue AS (
    SELECT 
        intEntitlementID,
        SUM([decCredit]) - SUM([decDebit]) AS VALUE
    FROM [tblMonetaryValueEvent] 
    WHERE intSchemeYear <= @intSchemeYear 
    GROUP BY intEntitlementID
),
EntitlementSummary AS (
    SELECT 
        ae.[intEntitlementID],
        SUM(ae.decIncrease) - SUM(ae.[decDecrease]) AS quantity,
        mv.VALUE,
        (SUM(ae.decIncrease) - SUM(ae.[decDecrease])) * mv.VALUE AS TOTAL
    FROM [tblAllocationEvent] ae
    LEFT JOIN MonetaryValue mv ON ae.intEntitlementID = mv.intEntitlementID
    WHERE ae.intBusinessID = @intBusinessID 
      AND ae.intSchemeYear <= @intSchemeYear 
    GROUP BY ae.intEntitlementID, mv.VALUE
    HAVING SUM(ae.decIncrease) - SUM(ae.[decDecrease]) > 0
)
SELECT SUM(TOTAL) AS TotalSum FROM EntitlementSummary;

这个方案把重复计算的部分抽出来单独计算一次，减少了数据库的运算量，尤其在数据量较大时优势明显。

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