VBA SQL执行Left Join关联表出现部分销售数量异常+1问题排查

问题背景 #

• 表1：客户信息数据，共约5万条记录
• 表2：按客户ID统计的销售数据，共约2.5万条记录

异常现象 #

基于客户ID将表2销售数据左连接到表1时，约500条记录的销售数量出现固定+1的偏差，其余记录关联结果完全正常。例如某客户在表2中销量为200，连接后输出结果为201。该SQL通过VBA执行，语句如下：

SELECT [Table1$].[ID], Name, Volume, Amount
FROM [Table1$] LEFT JOIN [Table2$] ON [Table1$].[ID] = [Table2$].[ID]

已验证异常ID在表2中无重复记录，无相邻ID的匹配错误问题。 #

可能的故障原因 #

• 浮点精度误差导致的显示偏差
  表2的销量字段如果是公式计算生成，会存在Excel原生的浮点精度问题：比如实际存储值为20.99999999999996，单元格设置为整数格式时会显示为20，但SQL查询会读取原始浮点值，输出到目标单元格时自动四舍五入为21，就会出现看似固定+1的偏差。
• 关联字段类型不匹配触发异常容错
  如果两张表的ID关联字段存储类型不一致（一张为数值型、一张为文本型），VBA调用的ACE/OLEDB驱动会自动做隐式类型转换，部分带不可见字符（空格、换行符）的ID会匹配失败，旧版本驱动的异常容错逻辑会错误读取相邻行的销量值，导致出现+1的偏移。
• 隐藏数据导致的结果累加
  如果表2开启了筛选、或者存在隐藏行，SQL查询会默认读取所有行（包括被筛选/隐藏的行），如果同一个ID对应两条销量记录（如1和20），手动查看时只能看到20的那条，但左连接会生成两条记录，后续如果做了自动去重合并操作，就会出现销量相加为21的情况。
• 旧版本ACE驱动的已知bug
  2016及更早版本的Access Database Engine（ACE）驱动处理超过2万行的左连接查询时，存在小概率的数值字段偏移1的bug，尤其是当关联存在未匹配的空值行时，会错误将上一行的数值+1赋值给当前空值行。

排查建议 #

1. 直接将SQL查询结果输出到空白工作表，不做任何格式转换，查看销量字段的原始值，确认是否为接近整数的浮点数。
2. 统一两张表ID字段的格式为文本类型，重新运行查询验证异常是否消失。
3. 升级ACE驱动到最新版本，或改用Power Query做关联操作，对比结果是否一致。

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