mysql - 计算每个人的运行总数

我需要显示数据库中每个人的跑步总数，但我只能得到所有人的跑步总数，所以这是我在图片上的表格
我已经有了这个问题：

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;

但问题是我只能得出这样的结果：
突出显示的数字应该是50，因为它是针对不同的学生

最佳答案

您必须以这样的方式编写查询：每次ID更改时都会初始化变量。
假设您可以使用以下列编写查询或视图：

id | studno | debit | credit
---+--------+-------+-------

那么，让我们编写查询：

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

关于mysql - 计算每个人的运行总数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28489626/