用于计算产品 ID 库存水平的增量/减量余额的 MySQL 查询

Question

我有一个“日志”表，用于跟踪库存变化。我用贷方表示库存增加，借方表示库存减少(我认为从会计原则来看，这是倒退的，但这是客户所希望的)。

我需要像运行总计一样计算每个产品的借方和贷方余额(由 upc 字段表示)——换句话说，我想显示按时间戳(或默认顺序)排序的日志，每当任何产品增加或减少时，显示由于该特定增加或减少而在该时刻产生的库存余额。

我已经能够使用此查询来演示这一点，但只能通过将其 UPC 代码添加为 where 子句中的条件来挑出特定产品:

SELECT 
    l.id,
    l.upc,
    l.credit,
    l.debit,
    @bal := @bal + credit - debit AS balance,
    l.timestamp
FROM log l, (SELECT @bal := 0) b
where upc = '677797003424';

我有一个 fiddle here

问题是，当我去掉 where 子句时，计算并没有考虑到它只应该分别对每组 UPC 代码进行操作。

经过一番搜索，我找到了 this question答案看起来与我可能需要的类似，但是，它与 MySQL 无关，而是与 SQL Server 相关。另外，我不确定结果会是我上面提到的日志/跟踪格式。具体来说，我想避免的是在每一行显示最终余额。如果库存中有 999 件产品，经过四次减量后，库存量为 995 件，则表示记录减量的每条记录不应显示计算出的 995 余额，而是第一个减量日志应显示结果余额998，第二个应该显示 997，等等。

--- 编辑---

有没有不使用变量的方法来完成这个？另外，是否可以让日志按时间戳排序，让产品都混在一起，而不是一起排序？

Answer 1

这对你有用吗？

SELECT 
    l.id,
    l.upc,
    l.credit,
    l.debit,
    @bal := IF(@previous=l.upc, @bal + l.credit - l.debit, l.credit - l.debit) AS balance,
    @previous := l.upc,
    l.timestamp
FROM `log` l, (SELECT @bal := 0, @previous='') b
ORDER BY l.upc ASC, l.timestamp ASC

编辑:

为了在@bal 中获得负值，您可以执行以下操作

SELECT 
    l.id,
    l.upc,
    l.credit,
    l.debit,
    @bal := IF(@previous=l.upc, @bal + l.credit - l.debit, l.credit - l.debit) AS balance,
    @previous := l.upc,
    l.timestamp
FROM `log` l, (SELECT @bal := CAST(0 AS DECIMAL), @previous='') b
ORDER BY l.upc ASC, l.timestamp ASC