如何统计MySQL转账表中各账户的收支累计金额？

完整SQL语句 #

SELECT
    id_account,
    IFNULL(SUM(income_amount), 0.00) AS income_amount,
    IFNULL(SUM(outcome_amount), 0.00) AS outcome_amount
FROM (
    -- 转出账户的支出记录
    SELECT
        id_account_origin AS id_account,
        0.00 AS income_amount,
        amount AS outcome_amount
    FROM transfers
    WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
    
    UNION ALL
    
    -- 转入账户的收入记录
    SELECT
        id_account_destination AS id_account,
        amount AS income_amount,
        0.00 AS outcome_amount
    FROM transfers
    WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
) AS account_transactions
GROUP BY id_account
ORDER BY id_account;

逻辑说明 #

1. 拆分转账记录：通过UNION ALL将每条转账拆分为两条独立记录：

   • 一条以转出账户id_account_origin作为统计账户，金额计入支出项，收入项设为0
   • 一条以转入账户id_account_destination作为统计账户，金额计入收入项，支出项设为0
     这种方式能确保所有参与过转账的账户都被纳入统计，不会遗漏仅作为转出或仅作为转入的账户。

2. 聚合计算：外层查询按id_account分组，用SUM分别累加总收入和总支出。IFNULL函数用于处理单一收支的账户，将NULL值转为0.00，保证输出格式统一。

3. 时间范围控制：子查询中的created_at条件可根据实际需求调整，比如用created_at >= '起始时间' AND created_at <= '结束时间'替代BETWEEN，避免边界时间的判定歧义。

4. 性能保障：原表已创建id_account_origin、id_account_destination和created_at的索引，该查询会自动利用这些索引，无需额外优化即可高效执行。

示例输出 #

执行后将得到符合需求的统计结果：

+------------+---------------+-----------------+
| id_account | income_amount | outcome_amount  |
+------------+---------------+-----------------+
|         19 |     27690.87  |        57204.80 |
|        112 |      1000.00  |         2349.00 |
|       1011 |    575877.56  |        17454.50 |
|         17 |    135002.61  |          204.30 |
+------------+---------------+-----------------+

内容的提问来源于stack exchange，提问作者Sebastián Sameghini