You need to enable JavaScript to run this app. SQL如何逐行扣减Qty、基于前序记录计算剩余金额?
优惠活动
大模型
产品
解决方案
定价
更多

SQL如何逐行扣减Qty、基于前序记录计算剩余金额?

逐行滚动计算剩余余额的实现方案

原查询缺陷

原有写法直接用全局固定的总记录数减1计算Remaining,完全没有关联每行Qty的累计扣减逻辑,因此所有行的返回值完全一致,无法实现逐行递减效果。

支持窗口函数的主流SQL实现

MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库都支持窗口函数,该方案写法简洁、性能最优:

  • 核心逻辑:通过窗口函数计算从首行到当前行的Qty累计值,用固定总基数减去累计值,得到逐行递减的剩余金额。
  • 必须显式指定行排序规则:数据库表本身无固有存储顺序,没有明确排序的逐行计算逻辑会出现结果随机不稳定的问题。
SELECT 
  date,
  EmployNumber,
  -- 保留原有Total值的计算逻辑
  (SELECT COUNT(*) FROM List) AS Total,
  Qty,
  -- 逐行扣减计算剩余值
  (SELECT COUNT(*) FROM List) 
    - SUM(Qty) OVER (
        -- 此处排序规则需和业务预期对齐,决定扣减的先后顺序
        ORDER BY date, EmployNumber 
      ) AS Remaining
FROM Employ
-- 最终结果排序和计算窗口的排序保持一致
ORDER BY date, EmployNumber

该SQL执行后可直接得到预期的逐行递减结果。

无窗口函数的旧版本SQL兼容方案

对于MySQL 5.x这类不支持窗口函数的旧版本数据库,可通过相关子查询实现累计Qty计算:

SELECT 
  e1.date,
  e1.EmployNumber,
  (SELECT COUNT(*) FROM List) AS Total,
  e1.Qty,
  (SELECT COUNT(*) FROM List) 
    - (SELECT SUM(e2.Qty)
       FROM Employ e2
       -- 匹配排序规则,累计当前行及之前所有记录的Qty
       WHERE (e2.date < e1.date)
          OR (e2.date = e1.date AND e2.EmployNumber <= e1.EmployNumber)
      ) AS Remaining
FROM Employ e1
ORDER BY e1.date, e1.EmployNumber

注意事项

  • 若Qty字段存在空值,累计求和时需做空值转0处理,例如替换为SUM(IFNULL(Qty, 0)),避免空值导致累计结果计算错误。
  • 若Total值并非固定的List表计数,可直接通过窗口函数SUM(全局总基数表达式) OVER ()获取全局值,减少重复标量子查询的执行开销。

内容的提问来源于stack exchange,提问作者salo

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.12 06:06:07