用于查询帐户余额的SQL查询 [英] SQL query for calculating account balance

问题描述

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

I want to calculate account balance using raw sql without extra application logic. Transaction schema includes amount, from_account_id and 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

它不能按我预期的那样工作-结果是错误的，但是如果我仅在事务正常运行后才加入交易，例如，借记金额就可以了

And it does not work as I expected - result is wrong, but if I join transaction only once it works correctly, for example just debit amount is ok

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

我在余额查询中错过了什么?

What did I miss in my balance query?

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

推荐答案

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

You are basically calculating the cross product between a tdebits and tcredits, i.e. for each row in tdebits you are iterating over all the rows in tcredits. There's also no reason to join to accounts (unless to_account_id and from_account_id aren't foreign keys).

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

You only need to do one pass over transactions and you just need to know whether the amount is a credit or debit.

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.

If an account can transfer to itself, add a t.to_account_id <> t.from_account_id.

这篇关于用于查询帐户余额的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！