mysql转账余额加减_用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?

解决方案

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