MariaDB 10.1.31 余额表计算异常，求正确SQL查询语句

你的原查询仅计算了单条交易记录的进出差值，并未实现按商品分组的累计余额计算——子查询按id分组的逻辑只能取单条记录的数值，无法对同商品的历史交易做累加，这是导致余额不正确的核心原因。

针对MariaDB 10.1.31（不支持窗口函数SUM() OVER()），可以通过用户变量实现按商品分组的累计余额计算，以下是符合预期结果的查询语句：

正确查询语句 #

SELECT id, item, iIN, iOut, date, Balance
FROM (
    SELECT 
        t.id,
        t.item,
        t.iIN,
        t.iOut,
        t.date,
        @balance := CASE 
            WHEN @current_item = t.item THEN @balance + IFNULL(t.iIN, 0) - IFNULL(t.iOut, 0)
            ELSE IFNULL(t.iIN, 0) - IFNULL(t.iOut, 0)
        END AS Balance,
        @current_item := t.item
    FROM 
        `transaction` t
    CROSS JOIN (
        SELECT @current_item := '', @balance := 0
    ) AS vars
    ORDER BY 
        t.item, 
        t.date ASC
) AS temp
ORDER BY id DESC;

逻辑说明 #

1. 内层查询通过用户变量@current_item跟踪当前处理的商品，@balance累计该商品的余额：
   • 若当前商品与上一条相同，在原有余额基础上加减当前交易的进出量
   • 若切换商品，重置余额为当前交易的进出差值
2. 内层先按item和date升序排序，保证交易按时间顺序累计
3. 外层按id降序排列，匹配你预期的结果顺序

原查询的问题点 #

• 子查询按id分组完全没必要（单条记录的SUM(iIN)就是iIN本身）
• 未按item分组统计同商品的历史交易
• 没有按时间顺序做累计计算，无法得到余额

内容的提问来源于stack exchange，提问作者Ohidul Islam