mysql - 连接多个表时数据量翻倍

Question

以 excel 格式链接到我所有的数据库表： 数据库

这是我的任务： 查找每个客户的总存款、总取款和总差额。

我所做的是

select c.customerid, customername, sum(d.depositamount) as 'Total Deposit', sum(w.withdrawalamount) as 'Total Withdraw', sum(d.depositamount) - sum(w.withdrawalamount) as 'differences'
from customers c left outer join deposits d on c.customerid = d.customerid 
left outer join withdrawals w on c.CustomerID = w.CustomerID 
group by c.CustomerID 
order by c.CustomerID;

结果

我的问题是“总存款”和“总提款”的数据翻了一番。由于这两列数据加倍，差值也加倍。我知道我可以将所有列除以 2 来解决问题，但我想知道这样做的正确方法。

我的问题是如何以不加倍数据的方式连接多个表？

（例如，“James Carlton Brokeridge”假设分别有 450、380 和 70）。

标签： mysqlsql

Answer 1

您得到的值不正确，因为每次客户进行多次存款或取款时，都会导致在连接两个表时复制另一个（取款/存款）表中的行。要解决此问题，请在子查询中求和：

select c.customerid, 
       c.customername, 
       d.totaldeposit as 'Total Deposit', 
       w.totalwithdrawal as 'Total Withdraw', 
       d.totaldeposit - w.totalwithdrawal as 'differences'
from customers c 
left outer join (
   select customerid, sum(depositamount) as totaldeposit
   from deposits
   group by customerid
) d on c.customerid = d.customerid 
left outer join (
   select customerid, sum(withdrawalamount) as totalwithdrawal
   from withdrawals
   group by customerid
) w on c.customerid = w.customerid 
order by c.customerid