php - 使用mysql计算trasaction wise balance

Question

MySQL 我想执行一个查询，通过该查询我可以通过公式确定 Total Out：

总打开数 = 最后记录（总打开数）+ 总出 - 总入

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,client_id INT NOT NULL
,action_type VARCHAR(12) NOT NULL
,action_amount INT NOT NULL
);

INSERT INTO my_table(client_id,action_type,action_amount) VALUES
(1            ,'credit',         1000),
(1            ,'debit',          100),
(1            ,'credit',         500),
(1            ,'debit',          300),
(2            ,'debit',          1000),
(2            ,'credit',         1200),
(3            ,'debit',          1000),
(3            ,'credit',         1000),
(4            ,'debit',          1000);

我要结果

Transaction_id | Client Id | Credit | Debit | Balance
       1       |    1      |  1000  |    0  | 1000 
       2       |    1      |     0  |  100  |  900 
       3       |    1      |   500  |    0  | 1400 
       4       |    1      |     0  |  300  | 1100

标签： phpmysql

Answer 1

从 MySQL 8.0 开始，您可以使用窗口函数作为解决方案：

select 
    transaction_id,
    client_id,
    if(action_type='credit', action_amount, 0) credit,
    if(action_type='debit', action_amount, 0) debit,
    sum(if(action_type='credit', action_amount, -action_amount)) over(partition by client_id order by transaction_id) balance
from transactions;

在线运行MySQL