计算每个人的运行总计 [英] Calculate running total for each person
本文介绍了计算每个人的运行总计的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要显示我的数据库中每个人的运行总计,但我只能得到他们所有的运行总计,所以这些是我的表上的图片
I need to show the running total for each person in my database but I could only get the running total for all of them, so these are my tables on the pic
我已经有这个查询:
SELECT
id,
studno,
if(type=0,amount,0)debit,
if(type=1,amount,0)credit,
if(type=0,@bal:=@bal+amount,@bal:=@bal-amount) runningTotal
FROM
(SELECT id, studno, amount, 0 type from tblPayables
UNION ALL
SELECT id, studno, amount, 1 type from tblPayments)s, (SELECT @bal:=0)b
ORDER BY studno, id, type;
但事情是我只能得到这个结果:
but the thing is I can only come up to this result:
突出显示number应该为50,因为它是一个不同的studno
The highlighted number should be 50 since it's for a different studno
推荐答案
您必须以这样一种方式写入您的查询,
You must write your query in such a way that the variable is initialized every time an Id changes.
假设您可以使用以下列写入查询或视图:
Let's say you can write a query or view with the following columns:
id | studno | debit | credit
---+--------+-------+-------
那么,让我们写下查询:
So, let's write the query:
select id, debit, credit
, @bal := ( -- If the value of the column `studno` is the same as the
-- previous row, @bal is increased / decreased;
-- otherwise, @bal is reinitialized
case
when @studno = studno then @bal + debit - credit
else debit - credit
end
) as balance
@studno := a.studno as studno -- It's important to update @studno
-- AFTER you update @bal
from
(
select @bal := 0
, @studno := 0 -- This variable will hold the previous
-- value of the `studno` column
) as init, -- You must initialize the variables
( -- The above mentioned query or view
select ...
from ...
) as a
order by a.studno, a.id -- It's REALLY important to sort the rows
这篇关于计算每个人的运行总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文