如何按行计算wallet表剩余金额并创建view？

你的wallet表结构如下：

// wallet
+----+----------+--------+
| id | user_id  | amount |
+----+----------+--------+
| 1  | 5        | 1000   |
| 2  | 5        | -200   |
| 3  | 5        | -100   |
| 4  | 5        | 500    |
+----+----------+--------+

需要创建视图得到每行的累计剩余金额，预期结果包含remaining_amount字段。

解决方案 #

方法一：使用窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等现代数据库）

窗口函数可以高效实现累计求和，直接按用户分区、按交易id排序计算累计值：

CREATE VIEW wallet_with_remaining AS
SELECT 
    id,
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY id) AS remaining_amount
FROM wallet;

• PARTITION BY user_id：确保只对同一个用户的交易进行累计计算
• ORDER BY id：保证按照交易的先后顺序（id递增）累加，得到每一步的剩余金额

方法二：使用关联子查询（适用于不支持窗口函数的旧版数据库，如MySQL 5.x）

如果你的数据库不支持窗口函数，可以用子查询实现：

CREATE VIEW wallet_with_remaining AS
SELECT 
    w1.id,
    w1.user_id,
    w1.amount,
    (SELECT SUM(w2.amount) FROM wallet w2 WHERE w2.user_id = w1.user_id AND w2.id <= w1.id) AS remaining_amount
FROM wallet w1
ORDER BY w1.id;

这个方法通过子查询，对每一行统计同一个用户中id小于等于当前行id的所有交易金额之和，得到剩余金额。但数据量较大时，性能会比窗口函数差。

内容的提问来源于stack exchange，提问作者Martin AJ