mysql - 计算账户余额的SQL查询

Question

我想使用原始 sql 计算帐户余额，无需额外的应用程序逻辑。交易模式包括金额、from_account_id 和 to_account_id

我的查询是

SELECT SUM(tdebit.amount) - SUM(tcredit.amount) as balance
FROM accounts as a
INNER JOIN transactions as tdebit ON a.id = tdebit.to_account_id
INNER JOIN transactions as tcredit ON a.id = tcredit.from_account_id
WHERE a.id = $1 AND tdebit.succeed = true AND tcredit.succeed = true

它并没有像我预期的那样工作——结果是错误的，但是如果我只在它正常工作后才加入交易，例如，只需要借记金额就可以了

SELECT SUM(tdebit.amount) as debit
FROM accounts as a
INNER JOIN transactions as tdebit ON a.id = tdebit.to_account_id
WHERE a.id = $1 AND tdebit.succeed = true

我在余额查询中遗漏了什么？

http://sqlfiddle.com/#!15/b5565/1

Answer 1

您基本上是在计算 tdebits 之间的叉积和 tcredits ，即对于 tdebits 中的每一行您正在遍历 tcredits 中的所有行.也没有理由加入 accounts (除非 to_account_id 和 from_account_id 不是外键)。

您只需要进行一次转账交易，您只需要知道金额是贷方还是借方。

SELECT SUM(CASE WHEN t.to_account_id = $1 THEN t.amount ELSE -t.amount END) AS amount
FROM transactions AS t
WHERE (t.to_account_id = $1 OR t.from_account_id = $1)
  AND t.succeed = true

如果一个账户可以向自己转账，添加一个t.to_account_id <> t.from_account_id .