如何在Oracle查询结果末尾添加SETTLEMENT_AMOUNT合计行？

需求说明 #

现有可正常执行的Oracle查询语句，需在输出结果末尾新增一行，计算SETTLEMENT_AMOUNT列的总计值。该列通过以下CASE逻辑生成：

TO_CHAR (
    CASE opt.oper_type
       WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount /100, 0)
       ELSE vf.sttl_amount /100
    END,
    '99G999D00') AS SETTLEMENT_AMOUNT

期望合计值为示例中的-157.76。

修改后的查询语句 #

通过CTE（公共表表达式）先计算基础数据及SETTLEMENT_AMOUNT的原始数值，再用UNION ALL拼接合计行，确保合计逻辑与原列完全一致：

WITH base_data AS (
    SELECT 
        oc.card_number,
        CASE opt.oper_type
            WHEN 'OPTP0000' THEN '05-PURCHASE' 
            WHEN 'OPTP0020' THEN '06-CREDIT VOUCHER' 
            ELSE 'other' END AS Transaction_Type,
        opt.oper_date AS TRANSACTION_DATE, 
        op.account_number, 
        CASE vf.sttl_currency
            WHEN '840' THEN 'USD'
        END AS SOURCE_AMOUNT_CURRENCY, 
        TO_CHAR (
            CASE opt.oper_type
                WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount /100, 0)
                ELSE vf.sttl_amount /100
            END,
            '99G999D00') AS SOURCE_AMOUNT,
        op.auth_code, 
        opt.mcc, 
        opt.terminal_number AS TERMINAL_ID, 
        vf.pos_terminal_cap, 
        vf.pos_entry_mode,
        opt.part_key AS CAPTURE_DATE, 
        TO_CHAR (
            CASE opt.oper_type
                WHEN 'OPTP0000' THEN COALESCE (-vf.oper_amount /100, 0)
                ELSE vf.oper_amount /100
            END,
            '99G999D00') AS TRANSACTION_AMOUNT,
        COALESCE(ccu.name,opt.oper_currency) TRANSACTION_CURRENCY,
        CASE vf.sttl_currency
            WHEN '840' THEN 'BMD'
        END AS SETTLEMENT_CURRENCY, 
        TO_CHAR (
            CASE opt.oper_type
                WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount /100, 0)
                ELSE vf.sttl_amount /100
            END,
            '99G999D00') AS SETTLEMENT_AMOUNT,
        -- 存储未格式化的原始数值，用于合计计算
        CASE opt.oper_type
            WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount /100, 0)
            ELSE vf.sttl_amount /100
        END AS settlement_amount_raw,
        opt.merchant_name,
        COALESCE(cc.visa_country_code,opt.merchant_country) merchant_country,
        opt.clearing_sequence_num, 
        opt.clearing_sequence_count, 
        opt.oper_type
    FROM   opr_operation opt 
           INNER JOIN opr_participant op
           ON op.oper_id = opt.id
           INNER JOIN opr_card oc
           ON oc.oper_id = opt.id
           INNER JOIN vis_fin_message vf
           ON     opt.id = vf.id
           INNER JOIN  com_country cc
           ON cc.code= opt.merchant_country 
              AND op.card_id = vf.card_id
           INNER JOIN  com_currency ccu
           ON ccu.code= opt.oper_currency 
              AND op.card_id = vf.card_id   
    WHERE  opt.clearing_sequence_num > 1
    AND    opt.part_key >= DATE '2024-03-01'
    AND    opt.msg_type IN ('MSGTPAMC', 'MSGTPACC')
    AND    op.participant_type = 'PRTYISS'
)
-- 输出原始业务数据
SELECT 
    card_number,
    Transaction_Type,
    TRANSACTION_DATE,
    account_number,
    SOURCE_AMOUNT_CURRENCY,
    SOURCE_AMOUNT,
    auth_code,
    mcc,
    TERMINAL_ID,
    pos_terminal_cap,
    pos_entry_mode,
    CAPTURE_DATE,
    TRANSACTION_AMOUNT,
    TRANSACTION_CURRENCY,
    SETTLEMENT_CURRENCY,
    SETTLEMENT_AMOUNT,
    merchant_name,
    merchant_country,
    clearing_sequence_num,
    clearing_sequence_count,
    oper_type
FROM base_data
UNION ALL
-- 输出合计行
SELECT 
    NULL, NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL,
    TO_CHAR(SUM(settlement_amount_raw), '99G999D00') AS SETTLEMENT_AMOUNT,
    '总计', NULL, NULL, NULL, NULL
FROM base_data
-- 确保合计行固定在结果最后
ORDER BY CASE WHEN card_number IS NULL THEN 1 ELSE 0 END;

关键说明 #

1. CTE复用逻辑：通过base_data存储原始数据，同时新增settlement_amount_raw字段保存未格式化的数值，避免重复编写CASE逻辑，也保证了合计计算的精度（直接对数值求和，而非格式化后的字符串）。
2. 合计行拼接：用UNION ALL将原始数据和合计行合并，合计行除SETTLEMENT_AMOUNT（显示求和结果）和merchant_name（显示“总计”标识）外，其余字段设为NULL。
3. 排序控制：通过ORDER BY中的条件判断，将合计行固定在结果集末尾。

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