如何在MS Access的SQL中对三个及以上同列结构的表进行求和？

延续两表求和的思路，通过UNION ALL将多个表的聚合结果合并，外层再按material分组求和即可。以下是针对3个表的实现示例：

SELECT 
    material, 
    SUM(table1_sale), 
    SUM(table2_sale),
    SUM(table3_sale)
FROM
    (SELECT
         material, SUM(sale) AS table1_sale, 0 AS table2_sale, 0 AS table3_sale
     FROM
         table1 
     GROUP BY
         material
     UNION ALL
     SELECT
         material, 0, SUM(sale) AS table2_sale, 0 AS table3_sale
     FROM
         table2 
     GROUP BY
         material
     UNION ALL
     SELECT
         material, 0, 0, SUM(sale) AS table3_sale
     FROM
         table3 
     GROUP BY
         material) x
GROUP BY 
    material
ORDER BY 
    material;

扩展到更多表的方法 #

每新增一个同结构的表，只需在子查询中添加一个UNION ALL分支：

• 该分支中仅对当前表的sale列求和，其他表对应的列填0
• 外层SELECT语句中新增SUM(对应表的sale列别名)即可

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