如何在SQL查询中新增多个SUM(CASE WHEN)计算列的合计列

SQL 执行逻辑中，同层 SELECT 语句无法直接引用当前查询刚定义的字段别名做计算，这是之前尝试计算合计失败的核心原因，以下两种方案均可实现需求：

方案1：直接聚合相加（无额外嵌套，改造成本最低） #

直接在原有 SELECT 列表末尾新增合计列即可，修改后完整代码如下：

SELECT
FS.CUNO CUSTOMER,
OC.CUNM CUSTOMER_NAME,
FS.CUCD CURRENCY,
SUM(
CASE 
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-1, GETDATE()) AND DATEADD(DAY, 0 , GETDATE()) THEN FS.CUAM ELSE 0 END) 
    AS [CURRENT],

SUM(
CASE 
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-30, GETDATE()) AND DATEADD(DAY, -1, GETDATE()) THEN FS.CUAM ELSE 0 END) 
    AS [1-30 DAYS],

SUM(
CASE 
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-60, GETDATE()) AND DATEADD(DAY, -31, GETDATE()) THEN FS.CUAM ELSE 0 END) 
    AS [31-60 DAYS],

SUM(
CASE
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-90, GETDATE()) AND DATEADD(DAY, -61, GETDATE()) THEN FS.CUAM ELSE 0 END) 
    AS [61-90 DAYS],

SUM(
CASE 
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-120, GETDATE()) AND DATEADD(DAY, -91, GETDATE()) THEN FS.CUAM ELSE 0 END)
    AS [91-120 DAYS],

SUM(
CASE 
    WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-999, GETDATE()) AND DATEADD(DAY, -121, GETDATE()) THEN FS.CUAM ELSE 0 END) 
    AS [+120 DAYS],

-- 新增合计列X
SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-1, GETDATE()) AND DATEADD(DAY, 0 , GETDATE()) THEN FS.CUAM ELSE 0 END)
+ SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-30, GETDATE()) AND DATEADD(DAY, -1, GETDATE()) THEN FS.CUAM ELSE 0 END)
+ SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-60, GETDATE()) AND DATEADD(DAY, -31, GETDATE()) THEN FS.CUAM ELSE 0 END)
+ SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-90, GETDATE()) AND DATEADD(DAY, -61, GETDATE()) THEN FS.CUAM ELSE 0 END)
+ SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-120, GETDATE()) AND DATEADD(DAY, -91, GETDATE()) THEN FS.CUAM ELSE 0 END)
+ SUM(CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-999, GETDATE()) AND DATEADD(DAY, -121, GETDATE()) THEN FS.CUAM ELSE 0 END)
AS [X]

FROM FSLEDG FS
LEFT JOIN OCUSMA OC ON OC.CUNO = FS.CUNO
WHERE FS.YEA4 = '2021'
GROUP BY FS.CUNO, OC.CUNM, FS.CUCD
HAVING MIN(FS.TRCD) = 10 AND MAX(FS.TRCD) = 10
ORDER BY FS.CUNO

方案2：CTE 嵌套（代码更简洁，避免重复写逻辑） #

如果不想重复编写大量聚合表达式，可以用公用表表达式先计算各分段金额，外层再做合计：

WITH CUSTOMER_AGE AS (
    SELECT
    FS.CUNO CUSTOMER,
    OC.CUNM CUSTOMER_NAME,
    FS.CUCD CURRENCY,
    SUM(
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-1, GETDATE()) AND DATEADD(DAY, 0 , GETDATE()) THEN FS.CUAM ELSE 0 END) 
        AS [CURRENT],
    SUM(
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-30, GETDATE()) AND DATEADD(DAY, -1, GETDATE()) THEN FS.CUAM ELSE 0 END) 
        AS [1-30 DAYS],
    SUM(
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-60, GETDATE()) AND DATEADD(DAY, -31, GETDATE()) THEN FS.CUAM ELSE 0 END) 
        AS [31-60 DAYS],
    SUM(
    CASE
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-90, GETDATE()) AND DATEADD(DAY, -61, GETDATE()) THEN FS.CUAM ELSE 0 END) 
        AS [61-90 DAYS],
    SUM(
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-120, GETDATE()) AND DATEADD(DAY, -91, GETDATE()) THEN FS.CUAM ELSE 0 END)
        AS [91-120 DAYS],
    SUM(
    CASE 
        WHEN CONVERT(DATE, CONVERT(VARCHAR(8), FS.DUDT), 112) BETWEEN DATEADD(DAY,-999, GETDATE()) AND DATEADD(DAY, -121, GETDATE()) THEN FS.CUAM ELSE 0 END) 
        AS [+120 DAYS]
    FROM FSLEDG FS
    LEFT JOIN OCUSMA OC ON OC.CUNO = FS.CUNO
    WHERE FS.YEA4 = '2021'
    GROUP BY FS.CUNO, OC.CUNM, FS.CUCD
    HAVING MIN(FS.TRCD) = 10 AND MAX(FS.TRCD) = 10
)
SELECT 
    *,
    [CURRENT] + [1-30 DAYS] + [31-60 DAYS] + [61-90 DAYS] + [91-120 DAYS] + [+120 DAYS] AS [X]
FROM CUSTOMER_AGE
ORDER BY CUSTOMER

两种方案计算结果完全一致，性能差异可忽略，可根据编码习惯选择使用。

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