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
相关产品推荐
相关产品推荐

