Microsoft Access SQL：跨3个关联表统计外键记录数计数异常问题

统计结果失真的核心原因 #

• 常规直接关联3张产品线测试表的写法，会因跨表记录交叉匹配产生笛卡尔积膨胀：同一Order Number在不同测试表存在多条记录时，关联后会生成远大于实际的匹配行数，直接计数就会出现数值虚高
• 此前使用Job Name作为关联键的遗留逻辑，容易因不同订单的Job Name重名，导致跨订单的测试记录被混算到同一统计结果中

适配业务场景的正确查询实现 #

基于核心关联键Order Number设计聚合优先的查询逻辑，先对单张测试表按Order Number做分组计数，再逐表关联主订单表，从根源上避免笛卡尔积问题，SQL写法适配MS Access语法：

SELECT 
  主订单表.*,
  Nz(LineA.已测试数,0) + Nz(LineB.已测试数,0) + Nz(LineC.已测试数,0) AS 已测试单位总数
FROM 
(
  主订单表
  LEFT JOIN (
    SELECT OrderNumber, Count(*) AS 已测试数
    FROM 产品线A测试单表
    GROUP BY OrderNumber
  ) AS LineA ON 主订单表.OrderNumber = LineA.OrderNumber
  LEFT JOIN (
    SELECT OrderNumber, Count(*) AS 已测试数
    FROM 产品线B测试单表
    GROUP BY OrderNumber
  ) AS LineB ON 主订单表.OrderNumber = LineB.OrderNumber
  LEFT JOIN (
    SELECT OrderNumber, Count(*) AS 已测试数
    FROM 产品线C测试单表
    GROUP BY OrderNumber
  ) AS LineC ON 主订单表.OrderNumber = LineC.OrderNumber
)
WHERE
  主订单表.JobName LIKE '*' & [请输入要检索的Job Name关键词] & '*'

落地调试的注意事项 #

• 不要在多表关联的大查询外层直接写全局Count，这种写法在多LEFT JOIN场景下一定会触发计数膨胀
• 关联前检查主订单表、3张测试表中的Order Number字段类型、长度完全匹配（比如统一为18位短文本），避免因字段类型不一致出现关联错位
• 必须使用Nz函数将无测试记录的订单对应的计数值转为0，防止Null值参与加法运算导致最终总数为空
• Access环境下模糊检索的通配符为*，不要套用其他数据库的%通配符，否则会出现Job Name检索无结果的问题

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