MySQL未结余额贷方借方余额

本文介绍了MySQL未结余额贷方借方余额的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我得到了一个名为"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.