php - 更新运行余额

Question

我目前在 mysql 中有这个表。我想制作一个 php 脚本，它会像示例 B 一样自动更新余额。

Example A
----+-------+-------------+---------+
| ID | Debit | Credit     |Balance  |
+----+-------+------------+---------+
|  1 |       | 35         |         |
|  2 | 65    | 0          |         |
|  3         | 35         |         |
|  4 | 65    | 0          |         |
|  5 | 65    | 0          |         |
|  6 | 65    | 0          |         |
-------------------------------------
Example B
----+-------+-------------+---------+
| ID | Debit | Credit     |Balance  |
+----+-------+------------+---------+
|  1 |       | 35         |  -35    |
|  2 | 65    | 0          |   30    |
|  3         | 35         |  -5     |
|  4 | 65    | 0          |   60    |
|  5 | 65    | 0          |  125    |
|  6 | 65    | 0          |  190    |
-------------------------------------

这是我尝试过的:

 $sql = "
SELECT * 
  FROM tbl_journal 
 ORDER 
  BY date ASC
"; 

$conn = dbconnect(); 
$result = $conn->query($sql); 
while ($row = $result->fetch_assoc()) {
 $tbl_id = $row['tbl_id']; 
 $balance = $row['balance']; 
 $credit = $row['credit']; 
 $debit = $row['debit']; 
 echo "<pre>".$tbl_id."*".$row['balance']."</pre>"; 
 $balance = (($balance + $debit) - $credit); 
 $sql = "
UPDATE tbl_journal 
   SET balance = '$balance' 
 WHERE tbl_journal.tbl_id = $tbl_id"; 
$conn->query($sql); 
}

提前谢谢您。

Answer 1

您可以通过初始化一个变量，然后使用该变量来保持运行总计来完成此操作...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,debit INT NULL
,credit INT NULL
);

INSERT INTO my_table VALUES
(1,NULL,35),
(2,65,NULL),
(3,NULL,35),
(4,65,NULL),
(5,65,NULL),
(6,65,NULL);


SELECT id
     , debit
     , credit
     , @balance := @balance+(COALESCE(debit,credit*-1)) balance 
  FROM my_table
     , (SELECT @balance :=0) vars
 ORDER 
    BY id;
+----+-------+--------+---------+
| id | debit | credit | balance |
+----+-------+--------+---------+
|  1 |  NULL |     35 |     -35 |
|  2 |    65 |   NULL |      30 |
|  3 |  NULL |     35 |      -5 |
|  4 |    65 |   NULL |      60 |
|  5 |    65 |   NULL |     125 |
|  6 |    65 |   NULL |     190 |
+----+-------+--------+---------+

实际上，我编写的方式有点不正确，理论上可能会导致错误，但实际上我还没有设法触发所述错误，而且我发现这种方式更容易阅读。