如何在ORACLE SQL中为所有记录添加总计行

如果需要在查询结果末尾添加一行总计，对"No. Of Cards"列求和，以下是两种实用的实现方法：

方法一：使用UNION ALL结合聚合查询 #

这种方式逻辑直观，先查询原有数据，再单独计算总计行并合并结果，适合简单的统计场景。

针对示例数据的代码： #

-- 查询原有两行数据
SELECT 'First card' AS "Card Type", 100 AS "No. Of Cards"
FROM DUAL
UNION ALL
SELECT 'Second card' AS "Card Type", 200 AS "No. Of Cards"
FROM DUAL
UNION ALL
-- 计算并添加总计行
SELECT 'Total' AS "Card Type", SUM(card_count) AS "No. Of Cards"
FROM (
    SELECT 100 AS card_count FROM DUAL
    UNION ALL
    SELECT 200 AS card_count FROM DUAL
);

如果数据来自实际表（假设表名为card_records）： #

SELECT "Card Type", "No. Of Cards"
FROM card_records
UNION ALL
SELECT 'Total' AS "Card Type", SUM("No. Of Cards") AS "No. Of Cards"
FROM card_records;

方法二：使用ROLLUP函数（推荐用于分组统计场景） #

ROLLUP是Oracle提供的分组扩展函数，能自动生成总计行，尤其适合存在分组逻辑的复杂统计需求，还能避免重复编写原有数据的查询语句。

针对示例数据的代码： #

SELECT 
    -- 用GROUPING_ID判断是否为总计行，替换显示文本
    CASE 
        WHEN GROUPING_ID("Card Type") = 1 THEN 'Total'
        ELSE "Card Type"
    END AS "Card Type",
    SUM("No. Of Cards") AS "No. Of Cards"
FROM (
    SELECT 'First card' AS "Card Type", 100 AS "No. Of Cards" FROM DUAL
    UNION ALL
    SELECT 'Second card' AS "Card Type", 200 AS "No. Of Cards" FROM DUAL
) t
GROUP BY ROLLUP("Card Type")
-- 确保总计行排在最后
ORDER BY GROUPING_ID("Card Type");

关键说明： #

• GROUPING_ID("Card Type")：当该行是ROLLUP生成的总计行时，返回值为1，否则为0，用来区分普通行和总计行。
• ORDER BY GROUPING_ID("Card Type")：保证总计行在结果集的最后显示。

两种方法都能得到你需要的结果：

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