我得到了一个名为“ 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和日期顺序来计算余额和未结余额。请帮忙。
在这里，您如何使用动态变量做到这一点
select
s.client_id,
s.date,
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_balance := if(@prev_client = t.client_id,@cur_bal := @tot_credit - @tot_debit,t.credit-t.debit) as balance,
@prev_client := t.client_id,
@prev_credit := t.credit,
@prev_debit := t.debit
from(
select * from stock order by client_id,date
)t,(select @prev_client:=0,@cur_bal:=0,@prev_credit:=0,@prev_debit:=0,@tot_credit:=0,@tot_debit:= 0)r
)s
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句