交易记录余额计算mysql_用mysql计算余额

简短的回答,是的

更长的答案,你可以使用一个变量来计算它,因为它向下迭代行,即

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,然后输出计算出的值.

另外值得确定ORDER是否一致,否则Balance会根据返回行的顺序而有所不同.例如,如果您想将其重新排序到前面,则可以将其用作子查询,然后外部查询处理计算值,从而确保平衡保持正确,即

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