Oracle SQL对列进行汇总并在列表底部显示总计

发布于04月24日

我使用它来创建每个成本值的总数:

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

它创建了这个:

我希望它看起来像这样:

这可能吗？我不知道从哪里开始.

一如既往，我们将不胜感激.

推荐答案

使用GROUP BY和ROLLUP:

SELECT 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
FROM   data
GROUP BY ROLLUP(item_no)

对于示例数据，其中:

CREATE TABLE data (item_no, cost_1, cost_2, cost_3, cost_4) AS
SELECT 1057, 8111.73, -8600, 1550, CAST(NULL AS NUMBER(8,2)) FROM DUAL UNION ALL
SELECT 1693, 7856.25, -8600, 1575, NULL FROM DUAL UNION ALL
SELECT 1701, 5206.20, -8600, 2000, NULL FROM DUAL UNION ALL
SELECT 1784, 6002.97, -8600, 1900, NULL FROM DUAL;

哪些输出: