Oracle SQL实现列汇总并在列表底部展示总计的方法咨询

可以通过两种常用方法实现需求，以下是具体方案：

方法一：用UNION ALL拼接总计行 #

这种方法兼容性强，适用于所有支持SQL的数据库，核心思路是先查询原有明细及累计数据，再单独查询一行总计数据，最后将两者拼接。

-- 查询明细行及累计值
SELECT
    ITEM_NO,
    COST_1,    
    COST_2,
    COST_3,
    COST_4,   
    SUM(COST_1) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) COST_1_TOTAL,
    SUM(COST_2) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) COST_2_TOTAL,
    SUM(COST_3) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) COST_3_TOTAL,
    SUM(COST_4) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) COST_4_TOTAL
FROM data
UNION ALL
-- 查询总计行，各列直接显示总和
SELECT
    '总计' AS ITEM_NO,
    SUM(COST_1) AS COST_1,
    SUM(COST_2) AS COST_2,
    SUM(COST_3) AS COST_3,
    SUM(COST_4) AS COST_4,
    SUM(COST_1) AS COST_1_TOTAL,
    SUM(COST_2) AS COST_2_TOTAL,
    SUM(COST_3) AS COST_3_TOTAL,
    SUM(COST_4) AS COST_4_TOTAL
FROM data
-- 确保总计行排在最后
ORDER BY CASE WHEN ITEM_NO = '总计' THEN 1 ELSE 0 END, loan_no;

注意事项 #

• 如果你的ITEM_NO字段本身可能出现'总计'字样，可以换成其他独特标识，比如'--总计--'
• UNION ALL要求前后两个查询的列数、数据类型完全一致，否则会报错

方法二：使用ROLLUP生成总计行 #

如果你的数据库支持ROLLUP语法（比如MySQL 8.0+、PostgreSQL、SQL Server等），可以用这种更简洁的方式，利用分组汇总自动生成总计行。

SELECT
    -- 汇总行显示'总计'，否则显示原ITEM_NO
    CASE WHEN GROUPING(ITEM_NO) = 1 THEN '总计' ELSE ITEM_NO END AS ITEM_NO,
    SUM(COST_1) AS COST_1,
    SUM(COST_2) AS COST_2,
    SUM(COST_3) AS COST_3,
    SUM(COST_4) AS COST_4,
    -- 明细行显示累计值，总计行显示总和
    CASE 
        WHEN GROUPING(ITEM_NO) = 1 THEN SUM(COST_1) 
        ELSE SUM(COST_1) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) 
    END AS COST_1_TOTAL,
    CASE 
        WHEN GROUPING(ITEM_NO) = 1 THEN SUM(COST_2) 
        ELSE SUM(COST_2) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) 
    END AS COST_2_TOTAL,
    CASE 
        WHEN GROUPING(ITEM_NO) = 1 THEN SUM(COST_3) 
        ELSE SUM(COST_3) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) 
    END AS COST_3_TOTAL,
    CASE 
        WHEN GROUPING(ITEM_NO) = 1 THEN SUM(COST_4) 
        ELSE SUM(COST_4) OVER (ORDER BY loan_no ROWS UNBOUNDED PRECEDING) 
    END AS COST_4_TOTAL
FROM data
GROUP BY ROLLUP(ITEM_NO, loan_no)
-- 过滤掉不必要的中间分组行，只保留明细和总计
HAVING GROUPING(loan_no) = 0 OR GROUPING(ITEM_NO) = 1
-- 确保总计行排在最后
ORDER BY CASE WHEN GROUPING(ITEM_NO) = 1 THEN 1 ELSE 0 END, loan_no;

关键说明 #

• GROUPING(字段)函数用来判断当前行是否是汇总行：返回1表示是汇总行，返回0表示是明细行
• ROLLUP会根据分组字段生成层级汇总，HAVING子句用来过滤掉中间的分组结果，只保留明细和最终总计

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