Oracle SQL中按instance和id_sup计算指定字段的小计与总计

针对按instance分组，每个id_sup下生成v01、value_01、value_02的小计，最后生成instance层级总计的需求，提供两种实现方案：

方案一：分层聚合+UNION ALL（逻辑清晰，易定制） #

WITH detail_data AS (
    SELECT 
        instance,
        id_sup,
        id,
        srt,
        v01,
        v02,
        v03,
        value_01,
        value_02,
        1 AS sort_flag,
        srt AS sort_key
    FROM t
),
sup_subtotal AS (
    SELECT 
        instance,
        NULL AS id_sup,
        'TOTAL' AS id,
        MAX(srt) + 0.5 AS srt,
        SUM(v01) AS v01,
        NULL AS v02,
        NULL AS v03,
        SUM(value_01) AS value_01,
        SUM(value_02) AS value_02,
        2 AS sort_flag,
        MAX(srt) + 0.5 AS sort_key
    FROM t
    GROUP BY instance, id_sup
),
instance_total AS (
    SELECT 
        instance,
        NULL AS id_sup,
        'TOTAL' AS id,
        99999 AS srt,
        SUM(v01) AS v01,
        NULL AS v02,
        NULL AS v03,
        SUM(value_01) AS value_01,
        SUM(value_02) AS value_02,
        3 AS sort_flag,
        99999 AS sort_key
    FROM t
    GROUP BY instance
)
SELECT 
    instance,
    id_sup,
    id,
    srt,
    v01,
    v02,
    v03,
    value_01,
    value_02
FROM (
    SELECT * FROM detail_data
    UNION ALL
    SELECT * FROM sup_subtotal
    UNION ALL
    SELECT * FROM instance_total
)
ORDER BY instance, sort_flag, sort_key;

逻辑说明 #

• 明细层：保留原始数据，添加排序标识确保明细行优先显示。
• id_sup小计层：按instance+id_sup聚合计算合计，设置排序键让小计行紧跟对应分组的明细行。
• instance总计层：按instance聚合所有数据的合计，用极大值排序键确保总计行在末尾。
• 合并三层数据后按规则排序，得到符合要求的层级展示。

方案二：ROLLUP聚合（代码简洁，Oracle原生支持） #

SELECT
    instance,
    CASE WHEN GROUPING(id_sup) = 1 AND GROUPING(id) = 0 THEN NULL ELSE id_sup END AS id_sup,
    CASE 
        WHEN GROUPING(id) = 1 AND GROUPING(id_sup) = 0 THEN 'TOTAL'
        WHEN GROUPING(id) = 1 AND GROUPING(id_sup) = 1 THEN 'TOTAL'
        ELSE id 
    END AS id,
    CASE 
        WHEN GROUPING(id) = 1 AND GROUPING(id_sup) = 0 THEN MAX(srt) + 0.5
        WHEN GROUPING(id) = 1 AND GROUPING(id_sup) = 1 THEN 99999
        ELSE srt 
    END AS srt,
    SUM(v01) AS v01,
    CASE WHEN GROUPING(id) = 1 THEN NULL ELSE v02 END AS v02,
    CASE WHEN GROUPING(id) = 1 THEN NULL ELSE v03 END AS v03,
    SUM(value_01) AS value_01,
    SUM(value_02) AS value_02
FROM t
GROUP BY ROLLUP(instance, id_sup, id, srt)
HAVING GROUPING(instance) = 0
ORDER BY 
    instance,
    GROUPING(id_sup),
    CASE WHEN GROUPING(id_sup) = 0 THEN id_sup ELSE 99999 END,
    GROUPING(id),
    srt;

逻辑说明 #

• ROLLUP(instance, id_sup, id, srt)自动生成明细行、id_sup小计、instance总计三层聚合结果。
• GROUPING()函数判断当前行是否为聚合行，通过CASE语句控制列的显示内容（如'TOTAL'文本、空值）。
• 排序规则确保明细行在前，小计行紧跟对应分组，总计行在最后。

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