mysql - 从借方和贷方表计算帐户余额

Question

我有一个这样的账户表

+-----------+--------------+
|     fld_id|         name |
+-----------+--------------+
|         1 |         Bank1|
|         2 |         Bank2|
|         4 |         Bank3|
+-----------+--------------+

收入收入表是这样的

+-----------+--------------+---------------+-------------+
| fld_id    | fld_type     | fld_account id| fld_amount  |
+-----------+--------------+---------------+-------------+
|         1 |         Salry|            1  |     400     |
|         2 |         Rent |            2  |     500     |
|         4 |       Others |            1  |    1000     |
+-----------+--------------+---------------+-------------+

这样的付款表

+-----------+--------------+---------------+-------------+
| fld_id    | fld_type     | fld_account id| fld_amount  |
+-----------+--------------+---------------+-------------+
|         1 |         Food |            2  |     200     |
|         2 |    Entertain |            2  |     300     |
|         4 |    Transport |            1  |     400     |
+-----------+--------------+---------------+-------------+

我想要一个包含收入、支出和余额总和的账户的最终余额表，如下表 --

+-----------+--------------+---------------+-------------+
| account   | Income       | Expence       | Balance     |
+-----------+--------------+---------------+-------------+
|     Bank1 |         1400 |          400  |    1000     |
|     Bank2 |          500 |          500  |       0     |
|     Bank3 |            0 |            0  |       0     |
+-----------+--------------+---------------+-------------+

到目前为止，我编写了这个查询并获取收入和支出，但没有找到任何方法来计算余额，我的查询和结果是 --query

SELECT fld_account as account, Income, Expense
from tbl_accounts
LEFT JOIN (SELECT fld_account_id, SUM(fld_amount) as Income FROM tbl_revenue tr GROUP BY tr.fld_account_id) tc on fld_id=tc.fld_account_id
left JOIN (SELECT fld_account_id, SUM(fld_amount) as Expense FROM tbl_payment tp GROUP BY tp.fld_account_id) td on fld_id=td.fld_account_id

结果如下 -

+-----------+--------------+---------------+
| account   | Income       |   Expense     | 
+-----------+--------------+---------------+
|     Bank1 |         1400 |         400   |
|     Bank2 |          500 |         500   |
|     Bank3 |         Null |         Null  |
+-----------+--------------+---------------+

如何计算付款和收入表中的余额并将其与我的账户表连接起来？非常感谢任何帮助。

Answer 1

只需使用coalesce():

SELECT fld_account as account, COALESCE(Income, 0) as Income,
       COALESCE(Expense, 0) as Expense,
       ( COALESCE(Income, 0) - COALESCE(Expense, 0) ) as balance
FROM tbl_accounts LEFT JOIN
     (SELECT fld_account_id, SUM(fld_amount) as Income
      FROM tbl_revenue tr
      GROUP BY tr.fld_account_id
     ) tc 
     ON fld_id = tc.fld_account_id LEFT JOIN
     (SELECT fld_account_id, SUM(fld_amount) as Expense
      FROM tbl_payment tp
      GROUP BY tp.fld_account_id
     ) td 
     ON fld_id = td.fld_account_id;

COALESCE() 是返回第一个非NULL 参数的 ANSI 标准函数。