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
我需要在我的数据库中显示每个人的运行总数,但是我只能得到所有这些人的运行总数,所以这是我在pic上的表。
I already have this query:
我已经有了这个查询:
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:
但问题是我只能得出这样的结果:
The highlighted number should be 50 since it's for a different studno
突出显示的数字应该是50,因为它是用于不同的研究项目
2 个解决方案
#1
2
You must write your query in such a way that the variable is initialized every time an Id changes.
您必须以这样的方式编写查询:每次Id更改时,都会对变量进行初始化。
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
#2
0
as I wrote in comments - existing query does interleaving wrong, it will fail if payment id for corresponding payable id is smaller
正如我在comments中所写的那样——现有查询确实存在交叉错误,如果对应的应付id的支付id较小,那么它将失败
here is query which does interleaving properly and calculate running total as @Barranka suggested:
这里有一个查询,它正确地交错并按照@Barranka的建议计算运行总量:
select studno, amount, @bal := (
case
when @studno = studno then @bal + amount
else amount
end
) as balance,
@studno := studno
from
(
select @bal := 0
, @studno := 0
) as init,
(
select 0 as t, studno, amount, (select count(*) from tblPayables as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayables as a
union all
select 1 as t, studno, -amount, (select count(*) from tblPayments as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayments as a
) as q
order by studno, trN, t
http://sqlfiddle.com/#!2/9e0cf1/1
http://sqlfiddle.com/ ! 2/9e0cf1/1
#1
2
You must write your query in such a way that the variable is initialized every time an Id changes.
您必须以这样的方式编写查询:每次Id更改时,都会对变量进行初始化。
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
#2
0
as I wrote in comments - existing query does interleaving wrong, it will fail if payment id for corresponding payable id is smaller
正如我在comments中所写的那样——现有查询确实存在交叉错误,如果对应的应付id的支付id较小,那么它将失败
here is query which does interleaving properly and calculate running total as @Barranka suggested:
这里有一个查询,它正确地交错并按照@Barranka的建议计算运行总量:
select studno, amount, @bal := (
case
when @studno = studno then @bal + amount
else amount
end
) as balance,
@studno := studno
from
(
select @bal := 0
, @studno := 0
) as init,
(
select 0 as t, studno, amount, (select count(*) from tblPayables as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayables as a
union all
select 1 as t, studno, -amount, (select count(*) from tblPayments as b where a.id>b.id and a.studno=b.studno) as trN
from tblPayments as a
) as q
order by studno, trN, t
http://sqlfiddle.com/#!2/9e0cf1/1
http://sqlfiddle.com/ ! 2/9e0cf1/1