如何通过两张表计算客户待付款总额？SQL查询问题求助

需求与表结构 #

• 需求：统计各客户的待付款总额，计算公式为 (发票表total_basic + freight_rate + delivery_rate) - 付款表payment_received总和
• 发票表（invoice）结构：

inv_no  |  date |  customer_name | total_basic | freight_rate | delivery_rate 

（注：原表结构中字段名total_baic为笔误，统一修正为total_basic）

• 付款表（payments）结构：

id | date | customer_name | payment_received | inv_no

遇到的问题 #

分别统计发票总金额和已收款的SQL可得到正确结果：

• 发票总金额查询：

SELECT customer_name, 
       Sum(freight_rate) + sum(total_basic) + sum(delivery_rate) AS total_pending 
FROM invoice 
GROUP BY customer_name

• 已收款查询：

SELECT customer_name, 
       Sum(payment_received) AS total_received 
FROM payments 
GROUP BY customer_name

但直接关联两张表查询时，出现金额翻倍错误：
错误SQL：

SELECT invoice.customer_name, 
       Sum(invoice.freight_rate)+Sum(invoice.total_basic)+Sum(invoice.delivery_rate) AS totalpending, 
       Sum(payments.payment_received) AS totalpayment 
FROM invoice, payments 
GROUP BY invoice.customer_name, payments.customer_name

错误结果：

customer_name | totalpending | totalpayment
John | 1800 | 1100

实际数据：John的发票仅有1笔（total_basic=600，其余运费、配送费为0），待付款应为600；付款有2笔（600+500=1100），但查询结果中发票总金额被错误计算为1800。

错误原因 #

未添加关联条件直接关联两张表，导致笛卡尔积：1条发票记录会与2条付款记录分别匹配，生成2条临时记录，统计时发票金额会被重复累加（若有N条付款记录，发票金额就会被累加N次），最终导致金额虚高。

正确SQL写法 #

先分别统计每个客户的发票总金额和收款总金额，再通过customer_name关联这两个统计结果，同时处理客户仅存在于其中一张表的情况（比如有发票未付款、或有付款无对应发票）：

方法1：子查询关联 #

SELECT 
    COALESCE(i.customer_name, p.customer_name) AS customer_name,
    COALESCE(i.total_pending, 0) AS total_pending,
    COALESCE(p.total_received, 0) AS total_received,
    COALESCE(i.total_pending, 0) - COALESCE(p.total_received, 0) AS due_amount
FROM 
    (SELECT customer_name, 
            Sum(total_basic + freight_rate + delivery_rate) AS total_pending 
     FROM invoice 
     GROUP BY customer_name) i
FULL OUTER JOIN 
    (SELECT customer_name, 
            Sum(payment_received) AS total_received 
     FROM payments 
     GROUP BY customer_name) p
ON i.customer_name = p.customer_name
ORDER BY customer_name;

方法2：CTE（公共表表达式）写法 #

WITH invoice_totals AS (
    SELECT customer_name,
           SUM(total_basic + freight_rate + delivery_rate) AS total_pending
    FROM invoice
    GROUP BY customer_name
),
payment_totals AS (
    SELECT customer_name,
           SUM(payment_received) AS total_received
    FROM payments
    GROUP BY customer_name
)
SELECT 
    COALESCE(it.customer_name, pt.customer_name) AS customer_name,
    COALESCE(it.total_pending, 0) AS total_pending,
    COALESCE(pt.total_received, 0) AS total_received,
    COALESCE(it.total_pending, 0) - COALESCE(pt.total_received, 0) AS due_amount
FROM invoice_totals it
FULL OUTER JOIN payment_totals pt
ON it.customer_name = pt.customer_name
ORDER BY customer_name;

说明 #

• COALESCE函数用于处理NULL值：如果某客户只有发票没有付款，total_received会被设为0；反之total_pending设为0，避免计算出现NULL。
• FULL OUTER JOIN确保所有客户都被统计到，无论其是否有发票或付款记录。如果只需要统计有发票的客户，可改用LEFT JOIN；只需要有付款的客户则用RIGHT JOIN。

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