Access中合并两个表并将第二表数值取负后按名称分组求和的方法

报错原因分析 #

你最初的SQL报错是因为子查询返回了两个字段（TheName和SUM(TheVal2)），而主查询的差值计算只能接收单个值，且子查询没有做名称匹配，会返回所有名称的聚合结果，自然无法直接运算。

方案1：按你提出的合并表思路实现（无需建临时表，兼容性最好） #

你的思路完全可行，不需要额外创建实体临时表，直接用UNION ALL在查询中合并两个表的数据即可，SQL如下：

SELECT TheName, SUM(TheVal) AS FinalResult
FROM (
    -- 取Table1的数值，保留正数
    SELECT TheName, TheVal1 AS TheVal FROM Table1
    UNION ALL
    -- 取Table2的数值，统一转换为负数
    SELECT TheName, -TheVal2 AS TheVal FROM Table2
) AS MergedTable
GROUP BY TheName

该写法可以自动覆盖所有存在于两个表的名称，无需额外处理空值，运行结果完全符合你的预期计算规则。

方案2：分别聚合后关联计算 #

你也可以先对两个表单独分组求和，再关联计算差值，需要用Access自带的Nz函数把空值转为0，SQL如下：

SELECT 
    Nz(t1.TheName, t2.TheName) AS TheName,
    Nz(t1.SumVal1, 0) - Nz(t2.SumVal2, 0) AS FinalResult
FROM 
    (SELECT TheName, SUM(TheVal1) AS SumVal1 FROM Table1 GROUP BY TheName) AS t1
    LEFT JOIN 
    (SELECT TheName, SUM(TheVal2) AS SumVal2 FROM Table2 GROUP BY TheName) AS t2
    ON t1.TheName = t2.TheName
UNION
SELECT 
    Nz(t1.TheName, t2.TheName) AS TheName,
    Nz(t1.SumVal1, 0) - Nz(t2.SumVal2, 0) AS FinalResult
FROM 
    (SELECT TheName, SUM(TheVal1) AS SumVal1 FROM Table1 GROUP BY TheName) AS t1
    RIGHT JOIN 
    (SELECT TheName, SUM(TheVal2) AS SumVal2 FROM Table2 GROUP BY TheName) AS t2
    ON t1.TheName = t2.TheName

因为Access不支持标准SQL的FULL JOIN，所以需要用LEFT JOIN+RIGHT JOIN+UNION的写法实现全关联，更推荐优先使用方案1，写法更简洁性能也更好。

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