如何用单条SQL查询从两张表分别统计账单与应付款总和并展示经销商名称

假设两张表的基础结构如下：

• billing（账单表）：包含dealer_name（经销商名称）、amount（账单金额）字段
• outstanding（应付款表）：包含dealer_name（经销商名称）、outstanding_amount（应付款金额）字段

方案1：LEFT JOIN 覆盖所有有账单记录的经销商 #

如果只需要统计有账单记录的经销商，同时补全其应付款数据（无对应记录时显示0），可以用以下查询：

SELECT
    b.dealer_name AS 经销商名称,
    b.total_billing AS 账单总金额,
    COALESCE(o.total_outstanding, 0) AS 应付款总金额
FROM
    (SELECT dealer_name, SUM(amount) AS total_billing
     FROM billing
     GROUP BY dealer_name) b
LEFT JOIN
    (SELECT dealer_name, SUM(outstanding_amount) AS total_outstanding
     FROM outstanding
     GROUP BY dealer_name) o
ON b.dealer_name = o.dealer_name;

方案2：FULL JOIN 覆盖所有存在记录的经销商 #

如果需要包含所有在账单表或应付款表中有记录的经销商（即使某张表无对应数据），用FULL JOIN：

SELECT
    COALESCE(b.dealer_name, o.dealer_name) AS 经销商名称,
    COALESCE(b.total_billing, 0) AS 账单总金额,
    COALESCE(o.total_outstanding, 0) AS 应付款总金额
FROM
    (SELECT dealer_name, SUM(amount) AS total_billing
     FROM billing
     GROUP BY dealer_name) b
FULL JOIN
    (SELECT dealer_name, SUM(outstanding_amount) AS total_outstanding
     FROM outstanding
     GROUP BY dealer_name) o
ON b.dealer_name = o.dealer_name;

关键说明 #

• COALESCE函数用于将NULL值替换为0，避免结果中出现空值
• 子查询先完成单表的分组统计，再通过经销商名称关联，保证查询效率
• 如果两张表中的经销商名称存在格式不一致（如大小写、空格），需要先做统一处理（比如用TRIM(UPPER(dealer_name))）

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