MySQL SUM 减去 SUM 和 join

Question

我正在尝试将两个查询组合在一起，但不确定方法。我对连接以及如何使用它们知之甚少。

我有两张 table 。

用户:

id, 
email, 
userNumber

余额:

id, 
userNumber, 
credit, 
debit (multiple entries in which I use SUM - SUM group by userNumber)

下面是我工作正常的单独查询，但我需要以某种方式将它们组合起来:

SELECT SUM(credit) - SUM(debit) as 'balance' FROM balances GROUP BY userNumber

SELECT u.id, u.email, u.userNumber,
g.userNumber AS groupID,
g.debit AS debit,
g.credit AS credit
FROM user AS u
LEFT JOIN balances AS g ON u.userNumber = g.userNumber

我基本上需要将第一个表转换为具有userNumber 和余额，然后使用它来连接另一个表。第二个表不需要借方或贷方，而是用余额代替。

Answer 1

你可以使用这个

SELECT u.id, u.email, u.userNumber,
g.userNumber AS groupID,
g.debit AS debit,
g.credit AS credit,
g.balance AS balance 
FROM user AS u
LEFT JOIN (SELECT userNumber, SUM(credit) - SUM(debit) as 'balance' FROM balances GROUP BY userNumber) AS g ON u.userNumber = g.userNumber