基于客户名实现SQL Union查询结果差值计算的问题求助

问题分析 #

你之前的查询错误在于直接关联未聚合的invoice和payments表，产生了笛卡尔积：同一个客户的多条发票记录会和多条付款记录两两匹配，导致求和时重复计算，最终结果异常。比如John的错误结果2260是正确值1130的两倍，说明该客户在其中一张表中有2条记录，求和时被重复计算了两次。

正确解法 #

先分别对两张表按客户名聚合计算总额，再关联两张聚合后的结果表做差值计算：

SELECT inv.client_name, 
       (pay.total_payments - inv.total_invoice) AS Expr1001
FROM (
    -- 计算每个客户的发票总金额
    SELECT client_name, 
           SUM(freight_rate + total_basic_amount + delivery_rate) AS total_invoice
    FROM invoice
    GROUP BY client_name
) inv
INNER JOIN (
    -- 计算每个客户的收款总金额
    SELECT client_name, 
           SUM(payment_received) AS total_payments
    FROM payments
    GROUP BY client_name
) pay ON inv.client_name = pay.client_name

结果验证 #

执行上述查询后，会得到你期望的结果：

client_name | Expr1001
John        | 1130
MAc         | 3300

扩展说明 #

如果存在客户仅在invoice或payments表中有数据的情况，可以将INNER JOIN替换为FULL JOIN，并使用COALESCE处理NULL值，避免结果缺失：

SELECT COALESCE(inv.client_name, pay.client_name) AS client_name, 
       COALESCE(pay.total_payments, 0) - COALESCE(inv.total_invoice, 0) AS Expr1001
FROM (
    SELECT client_name, 
           SUM(freight_rate + total_basic_amount + delivery_rate) AS total_invoice
    FROM invoice
    GROUP BY client_name
) inv
FULL JOIN (
    SELECT client_name, 
           SUM(payment_received) AS total_payments
    FROM payments
    GROUP BY client_name
) pay ON inv.client_name = pay.client_name

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