mysql 用于计算帐户余额的SQL查询

6jjcrrmo 于 2023-02-11 发布在 Mysql

关注(0)|答案(2)|浏览(164)

我想使用原始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

mysql

来源：https://stackoverflow.com/questions/35102056/sql-query-for-calculating-account-balance

2条答案

按热度按时间

7vhp5slm1#

如果要所有帐户的值，请在执行联接之前进行聚合：

select a.*, coalesce(tdebit.debit, 0) - coalesce(tcredit.credit, 0)
from accounts a left join
     (select t.to_account_id, sum(t.amount) as debit
      from transactions t
      group by t.to_account_id
     ) tdebit
     on a.id = tdebit.to_account_id left join
     (select t.from_account_id, sum(t.amount) as credit
      from transactions t
      group by t.from_account_id
     ) tcredit
     on a.id = tcredit.from_account_id;

赞(0）回复(0）举报 2023-02-11

34gzjxbg2#

你基本上是在计算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。

赞(0）回复(0）举报 2023-02-11

我来回答

mysql 用于计算帐户余额的SQL查询

2条答案

相关问题

热门标签

最新问答