mysql - 复杂的 MySQL 贷方借方余额

Question

我有一个名为“Stock”的表，如下所示。

+-----------+--------------+---------------+---------+
| client_id |       date   |       credit  |    debit|
+-----------+--------------+---------------+---------+
|         1 |   01-01-2015 |            50 |       0 |
|         2 |   01-01-2015 |           250 |       0 |
|         2 |   01-01-2015 |           500 |       0 |
|         2 |   02-01-2015 |             0 |     500 |
|         1 |   02-01-2015 |             0 |      40 |
|         1 |   02-01-2015 |             0 |      80 |
|         3 |   05-01-2015 |          3000 |       0 |
|         2 |   06-01-2015 |             0 |     350 |
|         4 |   06-01-2015 |             0 |    1000 |
|         4 |   06-01-2015 |             0 |    2000 |
|         4 |   07-01-2015 |           500 |       0 |
|         5 |   07-01-2015 |           500 |       0 |
|         5 |   08-01-2015 |           500 |       0 |
|         1 |   09-01-2015 |             0 |     100 |
+-----------+--------------+---------------+---------+

我期待的结果是这样的:

+---------+-----------+-------------+--------+---------+----------+
|client_id|  date     |Open_Balance | credit |  debit  | balance  |
+---------+-----------+-------------+--------+---------+----------+
|       1 |01-01-2015 |           0 |     50 |       0 |       50 |
|       1 |02-01-2015 |          50 |      0 |      40 |       10 |
|       1 |02-01-2015 |          10 |      0 |      80 |      -70 |
|       1 |09-01-2015 |         -70 |      0 |     100 |     -170 |
|       2 |01-01-2015 |           0 |    250 |       0 |      250 |
|       2 |01-01-2015 |         250 |    500 |       0 |      750 |
|       2 |02-01-2015 |         750 |      0 |     500 |      250 |
|       2 |06-01-2015 |         250 |      0 |     350 |     -100 |  
|       3 |05-01-2015 |           0 |   3000 |       0 |     3000 |
|       4 |06-01-2015 |           0 |      0 |    1000 |    -1000 |
|       4 |06-01-2015 |       -1000 |      0 |    2000 |    -3000 |
|       4 |07-01-2015 |       -3000 |    500 |       0 |    -2500 |
|       5 |07-01-2015 |           0 |    500 |       0 |      500 |
|       5 |08-01-2015 |         500 |    500 |       0 |     1000 |
+---------+-----------+-------------+--------+---------+---- -----+

我需要按 client_id 和日期顺序计算余额和未结余额，如上所示。请帮忙。

Answer 1

尝试运算符

喜欢

SELECT client_id,date,credit,debit,(credit-debit) as balance FROM  Stock;

谢谢。