【FastAdmin】深入解析SQL查询：准确计算账户余额

作者：田辛老师

1 引言

在这篇博客中，田辛老师将分享如何通过一个相对复杂的SQL查询来解决一个实际的业务需求：计算财务账户的最终余额。
这个需求来自于一个学生的提问。按说这个需求比较简单，但是她遇到了如下困难。

账户余额并不是账户表的一个字段，需要根据明细数据实时统计。
明细数据来源除了交易记录表以外，还需要考虑转账的数据。
转账数据是独立的表转账记录表，转账的情况在该表里面有体现，但是转账费还是在交易记录表。
转账数据分为不同状态，只有completed的数据才能被查询出来。
只有完成了上面4个挑战，她才能解决这个问题。

2 业务需求

我们的目标是计算每个账户的当前余额，这个余额应该包括所有收入和支出，并且特别要处理账户之间的转账活动。这是财务系统中的常见需求，因为用户需要实时了解每个账户的准确财务状态。
这个需求对老鸟不难，但是考虑到大三的新手。这个SQL也是个小坑。

3 基本的表结构

在我们的数据库中，我们主要处理以下几张表：

账户表 (fa_tdyefm_account)：
- id: 账户ID
- account_name: 账户名称
交易记录表 (fa_tdyefm_record)：
- account_id: 关联的账户ID
- amount: 交易金额
转账记录表 (fa_tdyefm_transfer)：
- from_account_id: 源账户ID
- to_account_id: 目标账户ID
- amount: 转账金额
- status: 转账状态（‘pending’, ‘completed’, ‘failed’）

4 SQL实现

为了计算每个账户的余额，田辛老师设计了以下SQL查询：

SELECT
    a.id AS AccountID,
    a.account_name AS AccountName,
    COALESCE(SUM(r.amount), 0) +
    COALESCE(SUM(CASE WHEN t.from_account_id = a.id AND t.status = 'completed' THEN -t.amount ELSE 0 END), 0) +
    COALESCE(SUM(CASE WHEN t.to_account_id = a.id AND t.status = 'completed' THEN t.amount ELSE 0 END), 0) AS Balance
FROM
    fa_tdyefm_account a
LEFT JOIN
    fa_tdyefm_record r ON a.id = r.account_id
LEFT JOIN
    fa_tdyefm_transfer t ON (a.id = t.from_account_id OR a.id = t.to_account_id) AND t.status = 'completed'
GROUP BY
    a.id, a.account_name;

5 主要的语法及技术思路

COALESCE 函数：确保在没有交易记录的情况下余额显示为0，而不是NULL。
LEFT JOIN：用于连接交易和转账记录，即使某些账户没有交易或转账记录也能被正确处理。
CASE 语句：在处理转账金额时，根据当前账户是源账户还是目标账户调整金额的正负，确保只计算状态为“completed”的转账。
SUM 函数：用于汇总每个账户的交易和转账金额，计算最终的余额。

6 结论