Oracle中如何为UNION ALL查询的别名列添加总计行？

错误原因 #

你遇到的ORA-00942错误，是因为Oracle不允许在同一个UNION ALL的后续分支中直接引用前面子查询的别名（比如你定义的t）——别名仅在当前SELECT语句的作用域内有效，无法跨分支访问。

解决方案1：使用WITH子句（CTE，推荐） #

这是可读性最优的写法，先把原统计结果定义为公共表表达式（CTE），再基于这个CTE同时查询明细行和总计行：

WITH city_stats AS (
    -- 替换成你原有的各城市行数统计逻辑
    SELECT '北京' AS CITY, COUNT(*) AS AMOUNT FROM A
    UNION ALL
    SELECT '上海' AS CITY, COUNT(*) AS AMOUNT FROM B
    UNION ALL
    SELECT '广州' AS CITY, COUNT(*) AS AMOUNT FROM C
)
-- 查询各城市明细行
SELECT CITY, AMOUNT FROM city_stats
UNION ALL
-- 查询总计行
SELECT 'TOTAL' AS CITY, SUM(AMOUNT) AS AMOUNT FROM city_stats
-- 强制让总计行排在最后
ORDER BY CASE WHEN CITY = 'TOTAL' THEN 1 ELSE 0 END, CITY;

解决方案2：使用GROUP BY ROLLUP（高效） #

这种方法只需执行一次原统计逻辑，通过ROLLUP直接生成总计行，性能更优：

SELECT 
    -- 判断是否为总计行，替换显示文本
    CASE WHEN GROUPING_ID(CITY) = 1 THEN 'TOTAL' ELSE CITY END AS CITY,
    SUM(AMOUNT) AS AMOUNT
FROM (
    -- 替换成你原有的各城市行数统计逻辑
    SELECT '北京' AS CITY, COUNT(*) AS AMOUNT FROM A
    UNION ALL
    SELECT '上海' AS CITY, COUNT(*) AS AMOUNT FROM B
    UNION ALL
    SELECT '广州' AS CITY, COUNT(*) AS AMOUNT FROM C
) t
GROUP BY ROLLUP(CITY)
-- 确保总计行在末尾
ORDER BY CASE WHEN CITY = 'TOTAL' THEN 1 ELSE 0 END;

补充说明 #

• GROUPING_ID(CITY)用于识别ROLLUP生成的总计行：当返回值为1时，说明当前行是聚合后的总计行。
• 必须添加ORDER BY子句，因为UNION ALL和ROLLUP都不保证结果的默认排序，通过CASE表达式可以强制将'TOTAL'行排在最后。

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