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?

回答1:

Short answer, yes

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

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.

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

回答2:

The most simple answer would be:

SELECT `ID`, 
       `In`, 
       `Out`, 
       @running_bal := @running_bal + (`In` - `Out`)  as `Balance`
FROM   tableName, (SELECT @running_bal := 0) tempName

回答3:

A simple LEFT JOIN will suffice:

SELECT t.ID, t.In, t.Out, (SUM(t2.In) - SUM(t2.Out)) Balance
FROM mytable t
    LEFT JOIN mytable t2 ON b2.ID <= b.ID
GROUP BY b.ID

Or subquery (which as it turns out is about twice as fast)

SELECT t.ID, t.In, t.Out,
    (SELECT SUM(t2.In) - SUM(t2.Out) FROM mytable t2 WHERE t2.ID <= t.ID) Balance
FROM mytable t;