如何在含多CTE的SQL Server查询结果底部添加总计行（不新增查询列）

针对你的简化示例，核心问题是原来的排序逻辑可能让总计行混在普通结果中，我们可以通过在ORDER BY中嵌入CASE表达式来强制让总计行固定在底部，同时完全不新增查询结果列。这里是修正后的可直接运行的代码：

WITH T1 AS ( SELECT DocNum, CardCode, CardName FROM OINV )
SELECT 
    CardName AS [Customer Name], 
    DocNum 
FROM T1
UNION ALL
SELECT 
    'Grand Total', 
    CAST(COUNT(DocNum) AS VARCHAR(10)) -- 根据DocNum实际类型调整转换规则，确保类型兼容
FROM T1
ORDER BY 
    -- 给总计行设置更高的排序权重，让它最后出现
    CASE WHEN [Customer Name] = 'Grand Total' THEN 1 ELSE 0 END,
    [Customer Name] -- 普通行按客户名称正常排序

关键逻辑说明： #

• CASE WHEN [Customer Name] = 'Grand Total' THEN 1 ELSE 0 END：这个表达式会给总计行赋值1，其他行赋值0。排序时0会排在1前面，因此所有普通行会先按客户名排序，总计行必然出现在最后。
• 注意UNION ALL两侧的列数据类型必须兼容：如果DocNum是数值类型，直接用COUNT(DocNum)即可（无需转换）；如果是字符串类型，就需要把统计值转成字符串，避免类型不匹配报错。

扩展到你的实际账龄场景 #

针对你提到的双CTE场景（第一个CTE提取单据+账龄数据，第二个CTE分类账龄区间），我们可以沿用同样的思路，提供两种实现方案：

方案1：UNION ALL手动添加总计行

适合需要完全自定义总计行内容的场景：

WITH T1 AS (
    -- 第一个CTE：获取未结单据的余额与账龄天数
    SELECT 
        DocNum,
        CardCode,
        CardName,
        OutstandingBalance,
        DATEDIFF(day, DueDate, GETDATE()) AS AgingDays
    FROM OINV
    WHERE OutstandingBalance > 0 -- 仅保留未结清的单据
),
T2 AS (
    -- 第二个CTE：按客户分组，分类账龄区间
    SELECT 
        t1.CardName AS CustomerName,
        SUM(CASE WHEN t1.AgingDays BETWEEN 0 AND 30 THEN t1.OutstandingBalance ELSE 0 END) AS CurrentAmt,
        SUM(CASE WHEN t1.AgingDays BETWEEN 31 AND 60 THEN t1.OutstandingBalance ELSE 0 END) AS ThirtyToSixty,
        SUM(CASE WHEN t1.AgingDays > 60 THEN t1.OutstandingBalance ELSE 0 END) AS OverSixty,
        SUM(t1.OutstandingBalance) AS TotalBalance
    FROM T1
    -- 可在此关联其他需要的表，比如客户主表
    -- JOIN OCRD c ON t1.CardCode = c.CardCode
    GROUP BY t1.CardName
)
-- 主查询：返回客户明细行 + 总计行
SELECT 
    CustomerName,
    CurrentAmt,
    ThirtyToSixty,
    OverSixty,
    TotalBalance
FROM T2
UNION ALL
-- 总计行：汇总所有客户的各账龄区间金额
SELECT 
    'Grand Total',
    SUM(CurrentAmt),
    SUM(ThirtyToSixty),
    SUM(OverSixty),
    SUM(TotalBalance)
FROM T2
-- 排序确保总计行固定在底部
ORDER BY 
    CASE WHEN CustomerName = 'Grand Total' THEN 1 ELSE 0 END,
    CustomerName

方案2：使用GROUP BY ROLLUP自动生成总计行

如果你的第二个CTE已经是按客户分组的结果，用ROLLUP可以更简洁地生成总计行，减少重复代码：

WITH T1 AS (
    -- 第一个CTE：获取未结单据的余额与账龄天数
    SELECT 
        DocNum,
        CardCode,
        CardName,
        OutstandingBalance,
        DATEDIFF(day, DueDate, GETDATE()) AS AgingDays
    FROM OINV
    WHERE OutstandingBalance > 0
),
T2 AS (
    -- 第二个CTE：按客户分组，分类账龄区间
    SELECT 
        t1.CardName AS CustomerName,
        SUM(CASE WHEN t1.AgingDays BETWEEN 0 AND 30 THEN t1.OutstandingBalance ELSE 0 END) AS CurrentAmt,
        SUM(CASE WHEN t1.AgingDays BETWEEN 31 AND 60 THEN t1.OutstandingBalance ELSE 0 END) AS ThirtyToSixty,
        SUM(CASE WHEN t1.AgingDays > 60 THEN t1.OutstandingBalance ELSE 0 END) AS OverSixty,
        SUM(t1.OutstandingBalance) AS TotalBalance
    FROM T1
    GROUP BY t1.CardName
)
SELECT 
    COALESCE(CustomerName, 'Grand Total') AS CustomerName, -- 把ROLLUP生成的NULL替换为总计标识
    SUM(CurrentAmt) AS CurrentAmt,
    SUM(ThirtyToSixty) AS ThirtyToSixty,
    SUM(OverSixty) AS OverSixty,
    SUM(TotalBalance) AS TotalBalance
FROM T2
GROUP BY ROLLUP(CustomerName) -- 自动生成总计行
ORDER BY 
    CASE WHEN CustomerName IS NULL THEN 1 ELSE 0 END, -- 让总计行最后出现
    CustomerName

方案选择建议： #

• 如果需要明细行+总计行（比如简化示例中每个单据一行加总计数），优先用UNION ALL方案，灵活性更高。
• 如果已经是分组后的聚合结果（比如实际场景中按客户分组的账龄数据），用ROLLUP方案更简洁，减少代码冗余。

另外，如果担心客户名称和总计标识冲突（比如存在名为"Grand Total"的客户），可以把总计标识改成更独特的内容，比如'*** 总计 ***'，同时同步调整ORDER BY中的CASE条件即可。

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