mysql - MySql 获取账户余额

Question

我想计算 3 个账户的余额。

我有 2 张桌子：

• 具有id、name和start-balance 的帐户
• 交易价值,收费帐户,类型和支付

要计算余额，我必须将起始余额（来自账户）与所有交易值相加，其中charge-account = account-id，paid = 1 和type = 1。然后我必须减去（正确的词？）所有Charge-account = account-id，paid = 1 和type = 0的交易值

最后，如果一切正常，我只想看看账户现在有多少余额。

我尝试了这个查询，但我得到了错误的结果，看起来它多次添加了 start-balance ...

SELECT  
SUM(IF(a.id = 1, IF(t.type = 1 AND t.charge_account = 1, t.value, 0) - IF(t.type = 0 AND t.charge_account = 1, t.value, 0), 0) + a.start-balance) as "balanc_1",
SUM(IF(a.id = 2, IF(t.type = 1 AND t.charge_account = 2, t.value, 0) - IF(t.type = 0 AND t.charge_account = 2, t.value, 0), 0) + a.start-balance) as "balance_2",
SUM(IF(a.id = 3, IF(t.type = 1 AND t.charge_account = 3, t.value, 0) - IF(t.type = 0 AND t.charge_account = 3, t.value, 0), 0) + a.start-balance) as "balance_3"
FROM test.transactions t, test.accounts a
WHERE t.paid = 1;

交易：

帐户：

它应该是怎样的：

标签： mysqlsum

Answer 1

SELECT a.id, 
       MAX ( a.`start-balance` ) + 
       SUM ( CASE WHEN t.type = 1 then  t.value
                  WHEN t.type = 2 then -t.value
                  ELSE 0
             END ) as balance
FROM accounts a
JOIN transactions t
  ON a.id = t.`charge-account`
WHERE a.id IN (1,2,3) 
  AND t.paid = 1
GROUP BY id