MS Access多表条件SUM聚合查询灵活实现方案（无需VBA）

直接用 条件聚合+预聚合子查询左连接 的写法即可，完全不需要VBA，性能和扩展性都远好于多层关联子查询的写法。 #

核心思路 #

• 先分别对两张表按joinKey维度做独立聚合，避免关联时重复扫描数据
• 对table2聚合时，用Access原生支持的IIF函数做条件判断，一次性计算出所有method对应的指标值，不需要为每个指标单独写关联子查询
• 两个聚合结果做左连接，用NZ函数处理无匹配记录的空值，保证返回结果和原写法逻辑完全一致

可直接运行的SQL代码 #

SELECT
  t1.joinKey,
  t1.CMP,
  t1.SMP,
  NZ(t2.CLP1, 0) AS CLP1,
  NZ(t2.CLP2, 0) AS CLP2,
  NZ(t2.SLP1, 0) AS SLP1,
  NZ(t2.SLP2, 0) AS SLP2
FROM
  (
    -- 预聚合table1的物料成本、售价数据
    SELECT 
      joinKey,
      SUM(cMaterialPrice) AS CMP,
      SUM(sMaterialPrice) AS SMP
    FROM table1
    GROUP BY joinKey
  ) AS t1
LEFT JOIN
  (
    -- 预聚合table2所有method对应的人工成本、售价数据
    SELECT
      joinKey,
      SUM(IIF(method = 1, cLabourPrice, 0)) AS CLP1,
      SUM(IIF(method = 2, cLabourPrice, 0)) AS CLP2,
      SUM(IIF(method = 1, sLabourPrice, 0)) AS SLP1,
      SUM(IIF(method = 2, sLabourPrice, 0)) AS SLP2
    FROM table2
    GROUP BY joinKey
  ) AS t2
ON t1.joinKey = t2.joinKey

扩展与性能说明 #

• 扩展性：后续如果新增method取值（比如3、4、5），只需要在t2的预聚合块里新增对应SUM(IIF(...))行即可，不需要改动整体查询结构，代码不会随method增多变得冗余
• 性能：原写法每计算一个method相关指标，就会对table2做一次关联遍历，method越多遍历次数越多，数据量上千后性能下降非常明显；优化后的写法只会对两张表各做一次聚合扫描，再做一次连接，性能不会随method数量增加出现明显衰减
• 兼容性：写法完全符合MS Access的SQL语法规范，不需要开启任何特殊配置，也不需要依赖VBA函数

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