用mysql计算余额 [英] Calculate balance with mysql

问题描述

我有一个包含以下数据的表:

I have a table which contains the following data:

ID      In       Out 
1      100.00    0.00   
2       10.00    0.00   
3        0.00   70.00    
4        5.00    0.00    
5        0.00   60.00   
6       20.00    0.00     

现在我需要一个查询，该查询会给我以下结果:

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

是否可以通过一个查询来执行此操作，而无需使用触发器或存储过程?

Is it possible to do this with one query, without using a trigger or stored procedures?

推荐答案

是的简短答案

更长的答案，您可以使用变量对行进行迭代，即

Longer answer, you can use a variable to tally it up as it iterates down the rows, i.e.

SELECT 
    `table`.`ID`,
    `table`.`In`,
    `table`.`Out`,
    @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
FROM `table`, (SELECT @Balance := 0) AS variableInit
ORDER BY `table`.`ID` ASC

, (SELECT @Balance := 0) AS variableInit确保在开始之前将@Balance初始化为0.然后将每一行的@Balance设置为@Balance + In - Out，然后输出计算出的值.

The , (SELECT @Balance := 0) AS variableInit ensures that @Balance is initialised to 0 before you start. For each row it then sets @Balance to be @Balance + In - Out, and then outputs the calculated value.

另外，有必要确保ORDER一致，否则Balance会根据返回行的顺序而有所不同.例如，如果您想将其重新排序，则可以将其用作子查询，因为外部查询将处理计算出的值，从而确保余额保持正确，即

Also it's worth making certain the ORDER is consistent as otherwise the Balance will vary depending on what order the rows are returned. If you wanted to then order it back to front, for example, you could use this as a subquery as then the outer query deals with the calculated values thus ensuring the Balance remains correct i.e.

SELECT
    `balanceCalculation`.`ID`,
    `balanceCalculation`.`In`,
    `balanceCalculation`.`Out`,
    `balanceCalculation`.`Balance`
FROM (
    SELECT 
        `table`.`ID`,
        `table`.`In`,
        `table`.`Out`,
        @Balance := @Balance + `table`.`In` - `table`.`Out` AS `Balance`
    FROM `table`, (SELECT @Balance := 0) AS variableInit
    ORDER BY `table`.`ID` ASC
) AS `balanceCalculation`
ORDER BY `balanceCalculation`.`ID` DESC

这篇关于用mysql计算余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！