MySQL未结余额贷方借方余额 [英] MySQL Open Balance Credit Debit Balance

问题描述

我得到了一个名为"Stock"的表，如下所示.

I got a table named "Stock" as shown below.

+-----------+--------------+---------------+---------+
| 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 |
+-----------+--------------+---------------+---------+

我期望的结果是这样的:

The result I am expecting is something like:

+---------+-----------+-------------+--------+---------+----------+
|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和日期顺序来计算余额和未结余额".请帮忙.

I need balances and 'Open balances' to be calculated by client_id and date order as shown above. Please help.

推荐答案

在这里您可以做到这一点.

Here how you can do it..

select 
s.client_id,
s.date,
s.op_balance as Open_Balance,
s.credit,
s.debit,
s.balance
from
(
  select 
  t.client_id,
  t.date,
  t.credit,
  t.debit,
  @tot_credit := if(@prev_client = t.client_id, @tot_credit + t.credit,t.credit) as tot_cred,
  @tot_debit := if(@prev_client = t.client_id,@tot_debit + t.debit,t.debit) as tot_deb,
  @cur_bal := if(@prev_client = t.client_id, @tot_credit - @tot_debit,t.credit-t.debit) as balance,
  (@cur_bal + t.debit) - t.credit as op_balance,
  @prev_client := t.client_id
  from(
    select * from stock order by client_id,date
  )t,(select @prev_client:=0,@cur_bal:=0,@tot_credit:=0,@tot_debit:= 0,@open_balance:=0)r
)s

演示

DEMO

我还注意到，您具有用于对每个客户ID进行排序的date列的相同数据，但是具有datetime作为date的好处，这样排序不会与相同的日期混淆，或者可能是表中的主键.

Also I have noticed that the same data you have date column which I have used to do the sort per client id, but its good to have datetime for date so that the sorting does not get confused with same date or may be a primary key in the table.

这篇关于MySQL未结余额贷方借方余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！