MySQL算法更新银行交易(最终余额)

Question

我还是MySQL的新手，目前正在处理更新银行交易中最终余额的问题。给了我下表。给定的表命名为"；交易"；

我将给出以下结果；预期结果

通过以下MySQL代码，我成功地处理了credit_balance列；

UPDATE transaction 
SET credit_debit = CASE money_in_or_out 
WHEN "OUT" THEN amount*(-1)
ELSE amount*(1)
END;

ALTER TABLE `sql_invoicing`.`transaction` 
CHANGE COLUMN `credit_debit` `credit_debit` DECIMAL(9,2) NULL DEFAULT NULL ;

至于余额列，我只写了以下内容；

UPDATE transaction 
SET balance = 
(SELECT balance + credit_debit
ORDER BY payment_id DESC
LIMIT 1);

其产生以下错误的结果；返回的结果

因此，有人可以提出解决这个问题的建议吗？谢谢

Answer 1

您正在描述一个窗口求和。

作为初学者：我不建议存储这样的信息，因为维护起来很乏味。相反，您可以在需要时在flyw上计算它，或者将逻辑放入视图中。

如果你运行的是MySQL 8.0，你可以使用窗口函数：

select t.*,
sum(case when money_in_or_out = 'OUT' then - amount else amount end) 
over(order by payment_id) as balance
from transaction t

在早期版本中，您可以使用相关的子查询：

select t.*,
(
select sum(case when money_in_or_out = 'OUT' then - amount else amount end)
from transaction t1
where t1.payment_id <= t.payment_id
) as balance
from transaction t

注意：人们可能会期望一家银行拥有不止一个客户。您的表应该有一个列来存储该信息，因此可以按每个客户计算余额。然后，您将修改窗口功能如下：

sum(case when money_in_or_out = 'OUT' then - amount else amount end) 
over(partition by customer_id order by payment_id) as balance

子查询将变为：

(
select sum(case when money_in_or_out = 'OUT' then - amount else amount end)
from transaction t1
where t1.customer_id = t.customer_id and t1.payment_id <= t.payment_id
) as balance