mysql - 计算余额；它只给出一个余额

Question

我想获取每笔交易的余额，但它只打印 2329.26。

它应该像这样打印:

Withdrawal    Income    Balance
              2 000     2 500
500                     500
              1 000     1 000

SQL

SELECT t1.*,
       SUM(t2.data_sum) AS sum

FROM transactions AS t1
INNER JOIN transactions AS t2

ON t1.datetime_completed >= t2.datetime_completed
AND t1.id_user >= t2.id_user
AND t1.id_account >= t2.id_account

GROUP BY t1.id_account, t1.id_user, t1.datetime_completed, t1.data_sum
ORDER BY t1.datetime_completed

数据库

`id` int(11) NOT NULL AUTO_INCREMENT,
`id_account` tinyint(4) DEFAULT NULL,
`id_user` tinyint(4) DEFAULT NULL,
`data_name` varchar(30) NOT NULL,
`data_sum` decimal(10,2) DEFAULT NULL,
`data_note` text NOT NULL,
`data_type` varchar(15) NOT NULL,
`is_payed` tinyint(4) DEFAULT NULL,
`is_completed` tinyint(4) DEFAULT NULL,
`datetime_completed` datetime NOT NULL

我希望它从下往上计数，所以最早的交易在底部。

SQL 应该如何计算正确的余额？

Answer 1

您好，我想您会找到答案 HERE

我还创建了 SQL Fiddle对于您的示例，以便您了解它是如何工作的。

这里是查询:

SELECT t1.completed, t1.name, 
       CASE WHEN t1.data_sum < 0 THEN ABS(t1.data_sum) ELSE 0.00 END AS wihtdrawl,
        CASE WHEN t1.data_sum > 0 THEN t1.data_sum ELSE 0.00 END AS income,
        @b := @b + t1.data_sum AS balance
 FROM (SELECT @b := 0) AS bal
 CROSS JOIN myTable AS t1
 ORDER BY t1.id;

GL!