基于COALESCE结果的LEFT JOIN查询需求及表结构说明

First off, let's nail down the core goal: we need to retain all fields from TBDA while pulling in the sum_payment value—prioritizing matches from TBDA_REPAYMENT, and using TBDA_PAYNEW only when there's no match in the repayment table.

Simplest & Most Efficient Query #

This approach uses two LEFT JOINs to link the tables, then COALESCE to pick the first non-null sum_payment value (which will be from TBDA_REPAYMENT if available):

SELECT 
    tbda.*,
    COALESCE(tr.sum_payment, tp.sum_payment) AS sum_payment
FROM TBDA tbda
LEFT JOIN TBDA_REPAYMENT tr 
    ON tr.isdn = tbda.isdn 
    AND tr.tr_month = tbda.tr_month 
    AND tr.tr_year = tbda.tr_year
LEFT JOIN TBDA_PAYNEW tp 
    ON tp.isdn = tbda.isdn 
    AND tp.tr_month = tbda.tr_month 
    AND tp.tr_year = tbda.tr_year;

Why This Works: #

• LEFT JOIN ensures we keep every record from TBDA, even if there's no matching entry in either of the other two tables.
• COALESCE returns the first non-null value in its arguments—so if TBDA_REPAYMENT has a match, we use that sum_payment; if not, we fall back to TBDA_PAYNEW; if neither has a match, sum_payment will be NULL.

Handling Duplicate Records (Important!) #

If TBDA_REPAYMENT or TBDA_PAYNEW might have multiple rows for the same (isdn, tr_month, tr_year) combo, the above query could return duplicate TBDA records (due to a Cartesian product). To fix this, first aggregate the sum in subqueries:

SELECT 
    tbda.*,
    COALESCE(tr.sum_payment_total, tp.sum_payment_total) AS sum_payment
FROM TBDA tbda
LEFT JOIN (
    -- Aggregate sum_payment for each unique combo in TBDA_REPAYMENT
    SELECT isdn, tr_month, tr_year, SUM(sum_payment) AS sum_payment_total
    FROM TBDA_REPAYMENT
    GROUP BY isdn, tr_month, tr_year
) tr 
    ON tr.isdn = tbda.isdn 
    AND tr.tr_month = tbda.tr_month 
    AND tr.tr_year = tbda.tr_year
LEFT JOIN (
    -- Aggregate sum_payment for each unique combo in TBDA_PAYNEW
    SELECT isdn, tr_month, tr_year, SUM(sum_payment) AS sum_payment_total
    FROM TBDA_PAYNEW
    GROUP BY isdn, tr_month, tr_year
) tp 
    ON tp.isdn = tbda.isdn 
    AND tp.tr_month = tbda.tr_month 
    AND tp.tr_year = tbda.tr_year;

This ensures each TBDA record only appears once, even if the other tables have multiple matching entries.

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