为何SQL SUM函数返回错误值？多表关联求和问题排查

Ah, I see the issue here—your joins are causing a cartesian product between the deposit and credit tables, which makes the SUM functions count values multiple times. Let me break this down and fix it for you.

Why Your Original Query Returns Wrong Sums #

When you join customers directly to both deposit and credit, each row from deposit gets paired with every matching row from credit for the same customer. For example:

• If a customer has 2 deposit entries and 3 valid credit entries, the join creates 2×3=6 rows.
• The SUM(d_amount) will add each deposit 3 times, and SUM(c_amount) will add each credit 2 times. That's why your totals are inflated and incorrect.

Corrected Query Using Subqueries #

The fix is to calculate totals for each table separately first, then join those aggregated results to the customers table. This avoids the cross-multiplication of rows.

SELECT 
    c.name,
    COALESCE(d.total_depot, 0) AS total_depot,
    COALESCE(ct.total_credit, 0) AS total_credit
FROM customers c
LEFT JOIN (
    -- Calculate total deposits per customer first
    SELECT customer_id, SUM(d_amount) AS total_depot
    FROM deposit
    GROUP BY customer_id
) d ON d.customer_id = c.id
LEFT JOIN (
    -- Calculate total valid credits per customer first
    SELECT customer_id, SUM(c_amount) AS total_credit
    FROM credit
    WHERE type = 'credit'
    GROUP BY customer_id
) ct ON ct.customer_id = c.id;

Key Details:

• LEFT JOIN ensures we retain customers who have no deposits, no credits, or both (instead of filtering them out with INNER JOIN).
• COALESCE replaces NULL values (for customers with no deposits/credits) with 0, making the output cleaner and more intuitive.
• Each subquery groups by customer_id first, so we only get one aggregated total per customer for each table.

Alternative: Readable CTE Version (For Modern Databases) #

If your database supports Common Table Expressions (CTEs) (like PostgreSQL, MySQL 8+, SQL Server), you can write this in a more organized way:

WITH deposit_totals AS (
    SELECT customer_id, SUM(d_amount) AS total_depot
    FROM deposit
    GROUP BY customer_id
),
credit_totals AS (
    SELECT customer_id, SUM(c_amount) AS total_credit
    FROM credit
    WHERE type = 'credit'
    GROUP BY customer_id
)
SELECT 
    c.name,
    COALESCE(dt.total_depot, 0) AS total_depot,
    COALESCE(ct.total_credit, 0) AS total_credit
FROM customers c
LEFT JOIN deposit_totals dt ON dt.customer_id = c.id
LEFT JOIN credit_totals ct ON ct.customer_id = c.id;

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