如何保持交易的运行余额并在交易后获得金额

Question

我必须编写一个查询，该查询使用变量列出由ID 为 1 的客户执行的所有事务，按日期排序。该变量应保持运行余额，以显示客户在每次交易后的新余额。最终输出应给出交易的日期、金额和交易后的当前余额。

桌子

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| customer_id  | int(10) unsigned | YES  | MUL | NULL              |                             |
| last_created | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| amount       | decimal(6,2)     | NO   |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+

查询

SELECT last_created, amount , sum(amount) as moneyspent FROM transactions where 
customer_id = 1   ORDER BY  last_created;

Answer 1

SET @total = 0; 
SELECT last_transaction, amount ,@total := @total + amount AS runningtotal 
FROM sakila_payment 
ORDER BY last_transaction;