SQL关联收据表后Sum Over Partition计算交易金额重复翻倍问题

你的问题核心是多表关联时产生了笛卡尔积：一笔交易对应3张收据，关联后交易表的1条记录会和收据表的3条记录匹配，导致交易金额被重复计算3次（5×3=15）。select distinct只能去重整条记录，没法解决金额重复累加的问题，用子查询分步骤聚合是最直接的解法，下面给你两种场景的实现：

场景1：只需要交易总金额和对应收据总金额 #

先分别计算每个交易的正确金额、每个交易对应的收据总金额，再把两个结果关联，避免重复计算：

SELECT 
    t.customer_trx_id,
    t.transaction_total, -- 正确的交易金额，不会重复
    COALESCE(r.receipt_total, 0) AS receipt_total -- 该交易的所有收据总和
FROM (
    -- 子查询1：计算每个交易的正确金额
    SELECT 
        rcta.customer_trx_id,
        SUM(rctl.amount) AS transaction_total
    FROM ra_customer_trx_all rcta
    JOIN ra_customer_trx_lines_all rctl 
        ON rcta.customer_trx_id = rctl.customer_trx_id
    GROUP BY rcta.customer_trx_id
) t
LEFT JOIN (
    -- 子查询2：计算每个交易对应的收据总金额
    SELECT 
        araa.customer_trx_id,
        SUM(acra.amount) AS receipt_total
    FROM ar_receivable_applications_all araa
    JOIN ar_cash_receipts_all acra 
        ON araa.cash_receipt_id = acra.cash_receipt_id
    GROUP BY araa.customer_trx_id
) r ON t.customer_trx_id = r.customer_trx_id;

场景2：需要保留每张收据的明细，同时显示正确的交易金额 #

如果要展示每张收据的具体金额，同时显示交易的正确总金额，可以在关联交易金额子查询后，用MAX()或MIN()取交易金额（因为同一交易的金额是固定的）：

SELECT 
    araa.customer_trx_id,
    MAX(t.transaction_total) AS transaction_total, -- 正确的交易金额，不会重复
    acra.amount AS single_receipt_amount, -- 单张收据的金额
    SUM(acra.amount) OVER (PARTITION BY araa.customer_trx_id) AS total_receipts -- 该交易的收据总和
FROM ar_receivable_applications_all araa
JOIN ar_cash_receipts_all acra 
    ON araa.cash_receipt_id = acra.cash_receipt_id
JOIN (
    -- 子查询：先算出每个交易的正确金额
    SELECT 
        rcta.customer_trx_id,
        SUM(rctl.amount) AS transaction_total
    FROM ra_customer_trx_all rcta
    JOIN ra_customer_trx_lines_all rctl 
        ON rcta.customer_trx_id = rctl.customer_trx_id
    GROUP BY rcta.customer_trx_id
) t ON araa.customer_trx_id = t.customer_trx_id;

关键逻辑说明 #

• 先通过子查询对交易表、收据表分别做分组聚合，得到每个交易唯一的金额记录，再关联就不会产生笛卡尔积。
• COALESCE(r.receipt_total, 0)是为了处理没有收据的交易，避免显示NULL。
• 场景2里用MAX()是因为同一交易对应的transaction_total是相同的，取最大值/最小值都能得到正确的单值，不会因为多条收据重复累加。

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