如何为临时表查询结果添加Weekly_Total列的总计行？

很容易搞定这个需求！针对你的临时表#bl_reptemp3，我给你两种常用的实现方法，帮你在查询结果末尾加上Weekly_Total的汇总行：

方法1：用UNION ALL合并原数据与汇总行 #

这种方式最直接，先查原始数据，再单独生成一行汇总结果，用UNION ALL把两者拼接起来：

SELECT Rep, Monday, Tuesday, Wednesday, Thursday, Friday, Weekly_Total 
FROM #bl_reptemp3
UNION ALL
-- 汇总行：Rep列显示"总计"，日期列可设为NULL（也可以按需改成对应列的总和），Weekly_Total计算全局总和
SELECT '总计' AS Rep, NULL AS Monday, NULL AS Tuesday, NULL AS Wednesday, NULL AS Thursday, NULL AS Friday, SUM(Weekly_Total) AS Weekly_Total
FROM #bl_reptemp3

如果需要同时汇总各日期列的数值，只需要把NULL换成对应的SUM函数即可，比如SUM(Monday) AS Monday。

要是担心汇总行位置不固定，可以加个排序字段强制让它排在最后：

SELECT * FROM (
    SELECT Rep, Monday, Tuesday, Wednesday, Thursday, Friday, Weekly_Total, 0 AS SortOrder
    FROM #bl_reptemp3
    UNION ALL
    SELECT '总计' AS Rep, NULL, NULL, NULL, NULL, NULL, SUM(Weekly_Total), 1 AS SortOrder
    FROM #bl_reptemp3
) AS CombinedResult
ORDER BY SortOrder, Rep

方法2：用ROLLUP生成分组汇总行 #

如果你的数据是按Rep分组的，ROLLUP是更优雅的方案，它会自动帮你生成汇总行：

SELECT 
    -- 用GROUPING函数判断是否为汇总行，是的话显示"总计"，否则显示原Rep值
    CASE WHEN GROUPING(Rep) = 1 THEN '总计' ELSE Rep END AS Rep,
    SUM(Monday) AS Monday,
    SUM(Tuesday) AS Tuesday,
    SUM(Wednesday) AS Wednesday,
    SUM(Thursday) AS Thursday,
    SUM(Friday) AS Friday,
    SUM(Weekly_Total) AS Weekly_Total
FROM #bl_reptemp3
GROUP BY ROLLUP(Rep)

这里的GROUPING(Rep)在汇总行时返回1，普通行返回0，所以我们可以用CASE语句把汇总行的Rep列替换成“总计”。这种方法的好处是一次查询就能完成原始数据和汇总的计算，不需要写两次查询逻辑。

小提醒 #

• 要保证数据类型匹配：比如Rep列如果是字符串类型，汇总行的“总计”也要是字符串；数值列的SUM计算要对应正确的类型。
• 如果你的Weekly_Total本身是各日期列的和，用SUM(Weekly_Total)得到的总和，和直接对各日期列求和再相加的结果是一致的。

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