咨询VBA、ADO结合SQL实现按日期排序逐行扣减库存计算各行BALANCE的方案

现有开发基础 #

• 已基于ADO导入物料基础库存清单，可通过SQL从其他工作表分组统计各物料的期初可用库存
• 目前初步探索两个实现方向：一是将库存、交易数据整合到同一数组中逐行遍历扣减计算余额，二是直接对ADO返回的Recordset做行级处理，暂未明确两种路径的可行性与优劣

核心计算规则 #

• 先将所有交易记录按交易日期排序，逐行从对应物料的可用期初库存中扣减本行交易数量，得到本行交易发生后的剩余库存余额
• 同物料的交易需按日期顺序接续扣减，不同物料的库存余额独立计算、互不干扰

可落地的实现方案 #

方案1：数组+字典逐行遍历（和现有ADO流程适配性最高，调试成本低） #

1. 前置准备：将期初库存存入VBA字典，以物料编码Item为键，对应初始库存值为值
2. 数据预处理：把所有交易记录（含物料、交易日期、交易数量）统一按交易日期升序排序
3. 逐行计算：遍历排序后的交易记录，根据当前行的物料编码，从字典中取出该物料的当前剩余库存，减去当前行的交易数量，结果即为当前行的Balance；计算完成后将更新后的剩余库存回写到字典，供后续同物料交易的余额计算使用

数值校验参考：

• 物料A期初库存为10，按日期排序后的交易依次为25日交易4、27日交易5、29日交易3，计算得到的余额依次为6、1、-2，和示例输出结果完全匹配
• 物料B期初库存为9，按日期排序后的交易依次为23日交易9、24日交易4，计算得到的余额依次为0、-4，和示例中B行的结果完全一致

方案2：SQL直接计算（适合大交易量场景，无需手动写遍历循环） #

核心逻辑为利用行级累计计算能力：对交易表按物料分组、按日期排序，逐行计算从最早交易到当前行的累计交易数量，再关联期初库存表，通过公式Balance = 期初库存 - 累计交易数量直接得到每行余额，不需要在VBA层做循环遍历。

自学参考关键词 #

• VBA 字典对象 按键存取动态值
• ADO Recordset 可更新游标 逐行遍历操作
• SQL 行级累计求和（窗口函数、自连接实现路径）
• 库存流水 逐笔结存计算逻辑

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