SQLite 3.1.1剩余余额查询求助：实现累计扣减逻辑

你遇到的核心问题是没有实现基于历史交易的累计剩余计算——你的原始SQL每次都是用总进货量减去当前单次的使用量，而不是减去到当前为止的累计使用量。由于SQLite 3.1.1还不支持窗口函数（窗口函数从3.25.0版本才引入），我们需要用自连接的方式来实现累计计算逻辑。

问题拆解 #

你的需求本质是：

• 剩余余额 = 总进货量 - 从第一次到当前交易的累计使用量
• 剩余余额不能小于0
• 后续交易的剩余值必须基于历史累计结果（和“总进货量减累计使用量”等价，只要交易按时间顺序计算）

解决方案SQL #

SELECT
    fm.maintenance_id,
    fm.stock_id,
    fm.quantity_used,
    fm.date_registered,
    fm.date_changed,
    inv.stock_name,
    -- 计算剩余余额，确保结果不小于0
    MAX(total.total_stock - COALESCE(SUM(fm2.quantity_used), 0), 0) AS Remaining
FROM filter_maintenance fm
-- 关联库存名称表
INNER JOIN inventories inv 
    ON fm.stock_id = inv.stock_id
-- 先统计每个库存的总进货量
INNER JOIN (
    SELECT stock_id, SUM(order_quantity) AS total_stock
    FROM inventories_order
    GROUP BY stock_id
) total 
    ON fm.stock_id = total.stock_id
-- 自连接获取当前交易及之前的所有使用记录，计算累计使用量
LEFT JOIN filter_maintenance fm2 
    ON fm.stock_id = fm2.stock_id 
    AND fm2.date_registered <= fm.date_registered
-- 按主记录字段分组，保证每个交易记录唯一
GROUP BY 
    fm.maintenance_id, 
    fm.stock_id, 
    fm.quantity_used, 
    fm.date_registered, 
    fm.date_changed, 
    inv.stock_name, 
    total.total_stock
-- 按库存和交易时间排序，确保计算顺序符合交易发生逻辑
ORDER BY fm.stock_id, fm.date_registered;

关键逻辑说明 #

1. 总进货量统计：通过子查询total单独计算每个stock_id的总进货数量，避免了原始SQL中关联inventories_order导致的重复计算问题。
2. 累计使用量计算：通过自连接fm2，筛选出同一库存下交易时间早于等于当前记录的所有使用记录，用SUM(fm2.quantity_used)得到累计使用量；COALESCE用来处理第一条交易记录（此时没有更早的记录，SUM结果为NULL，自动转为0）。
3. 负数兜底：用MAX(..., 0)确保剩余余额不会出现负数，即使累计使用量超过总进货量，也会显示0。
4. 顺序保证：最后按stock_id和交易时间排序，保证结果的顺序完全符合交易发生的先后逻辑。

验证你的示例场景 #

假设总进货量为11：

• 第一条交易使用1：累计使用量=1，剩余=11-1=10
• 第二条交易使用6：累计使用量=1+6=7，剩余=11-7=4
• 第三条交易使用0：累计使用量=7+0=7，剩余=11-7=4
  完全匹配你预期的计算流程。

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