mysql - 通过另一个表的 id 在一个基于结果的组中对两个不同表的两列求和

Question

我有 3 张桌子：

用户：

id                 | account_name
-------------------|----------------------|
18                 | panic                |

存款：

id                 | user_id     | amount
-------------------|---------------------------|
1                  | 18          | 100          
2                  | 18          | 100          

提款：

id                      | user_id          | amount
------------------------|--------------------------------|
1                       | 18          | 200          
2                       | 18          | 200          

我试图得到这样的结果：

id                      | totalDeposits  | totalWithdraws
------------------------|---------------------------|
18                      | 200            | 400          

现在，当我出于某种原因尝试获取总数时，它们会交叉相加，当然，如果没有行，它应该返回 0。

SELECT t0.id,IFNULL(SUM(t1.amount),0) AS totalWithdrawals,
IFNULL(SUM(t2.amount),0) AS totalDeposits
FROM users t0 
LEFT OUTER JOIN withdrawals t1 ON (t0.id = t1.user_id) 
LEFT OUTER JOIN deposits t2 ON (t0.id = t2.user_id) 
GROUP BY t0.id

知道如何进行交叉连接，或者我在哪里总结错误？

标签： mysqlsqljoinmariadbleft-join

Answer 1

尝试这个-

SELECT A.id,
(SELECT SUM(amount) FROM Deposits WHERE user_id = A.id) totalDeposits, 
(SELECT SUM(amount) FROM Withdrawals WHERE user_id = A.id) totalWithdraws 
FROM users A
WHERE A.id = 18 -- WHERE can be removed to get all users details