SQL按司机分组统计收支存提总和及关联表数据异常问题

嘿，我来帮你搞定这个SQL统计的难题！你遇到的两个问题其实是多表连接统计里的常见坑，咱们一步步来解决：

问题根源分析 #

• 部分表无记录时无结果：这是因为你可能用了INNER JOIN，只要有一个表没有匹配司机的记录，整行就会被过滤掉，导致看不到任何结果。
• 统计结果被行数相乘：直接连接多表后，每个表的多条记录会交叉匹配（比如司机A在收入表有2条记录，支出表有3条，连接后会生成6条记录），SUM时就会把金额重复计算，最终结果偏大。

最优解决方案 #

核心思路是先按司机分组统计每个表的单独总和，再用外连接合并结果，这样既避免了行数相乘，又能保留所有司机的记录（哪怕某个表没数据）。

方案1：无司机主表时用全外连接 #

如果没有专门的司机主表，用FULL OUTER JOIN合并各个表的统计结果：

SELECT
    -- 取存在的司机姓名，避免NULL
    COALESCE(i.driver_name, e.driver_name, d.driver_name, w.driver_name) AS driver_name,
    -- 把NULL转换成0，保证统计结果直观
    COALESCE(i.total_income, 0) AS total_income,
    COALESCE(e.total_expense, 0) AS total_expense,
    COALESCE(d.total_deposit, 0) AS total_deposit,
    COALESCE(w.total_withdraw, 0) AS total_withdraw
FROM
    -- 先统计收入表的司机收入总和
    (SELECT driver_name, SUM(income_amt) AS total_income FROM CarIncome GROUP BY driver_name) i
FULL OUTER JOIN
    -- 统计支出表的司机支出总和
    (SELECT driver_name, SUM(expense_amt) AS total_expense FROM CarExpense GROUP BY driver_name) e
ON i.driver_name = e.driver_name
FULL OUTER JOIN
    -- 统计存款表的司机存款总和
    (SELECT driver_name, SUM(deposit_amt) AS total_deposit FROM CarDeposit GROUP BY driver_name) d
ON COALESCE(i.driver_name, e.driver_name) = d.driver_name
FULL OUTER JOIN
    -- 统计取款表的司机取款总和
    (SELECT driver_name, SUM(withdraw_amt) AS total_withdraw FROM CarWithdraw GROUP BY driver_name) w
ON COALESCE(i.driver_name, e.driver_name, d.driver_name) = w.driver_name

方案2：有司机主表时用左连接（更推荐） #

如果有专门的司机主表（比如Drivers表，包含所有司机的姓名），用LEFT JOIN更稳妥，能确保所有司机都被统计到：

SELECT
    dr.driver_name,
    COALESCE(i.total_income, 0) AS total_income,
    COALESCE(e.total_expense, 0) AS total_expense,
    COALESCE(dep.total_deposit, 0) AS total_deposit,
    COALESCE(w.total_withdraw, 0) AS total_withdraw
FROM
    Drivers dr -- 司机主表，存储所有司机信息
LEFT JOIN
    (SELECT driver_name, SUM(income_amt) AS total_income FROM CarIncome GROUP BY driver_name) i
ON dr.driver_name = i.driver_name
LEFT JOIN
    (SELECT driver_name, SUM(expense_amt) AS total_expense FROM CarExpense GROUP BY driver_name) e
ON dr.driver_name = e.driver_name
LEFT JOIN
    (SELECT driver_name, SUM(deposit_amt) AS total_deposit FROM CarDeposit GROUP BY driver_name) dep
ON dr.driver_name = dep.driver_name
LEFT JOIN
    (SELECT driver_name, SUM(withdraw_amt) AS total_withdraw FROM CarWithdraw GROUP BY driver_name) w
ON dr.driver_name = w.driver_name

关键细节说明 #

1. 子查询分组统计：每个子查询先按司机分组计算总和，这样每个司机在每个子查询里只有一行记录，彻底避免了多表连接时的行数交叉相乘问题。
2. 外连接保留记录：FULL OUTER JOIN/LEFT JOIN能确保即使某个表没有该司机的记录，也不会过滤掉该行，而是用NULL填充，再通过COALESCE转换成0。
3. COALESCE处理NULL：把统计结果中的NULL转换成0，让最终报表更直观，不会出现空白值。

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