如何在多表联合查询中新增另一相同字段的统计列？

假设你要新增统计的共有字段名为profit（请替换为你实际的字段名称），修改后的SQL如下：

SELECT 
    material, 
    SUM(IIF(source = 'table1', sale, 0)) AS table1_sale, 
    SUM(IIF(source = 'table2', sale, 0)) AS table2_sale,
    SUM(IIF(source = 'table3', sale, 0)) AS table3_sale,
    -- 新增的字段统计列
    SUM(IIF(source = 'table1', profit, 0)) AS table1_profit,
    SUM(IIF(source = 'table2', profit, 0)) AS table2_profit,
    SUM(IIF(source = 'table3', profit, 0)) AS table3_profit
FROM
    (SELECT material, sale, profit, 'table1' AS source 
     FROM table1 
     UNION ALL
     SELECT material, sale, profit, 'table2'
     FROM table2
     UNION ALL
     SELECT material, sale, profit, 'table3'
     FROM table3) x
GROUP BY 
    material
ORDER BY 
    material;

关键修改说明 #

• 子查询部分：在每个SELECT语句中加入需要新增统计的共有字段（示例中为profit），确保UNION ALL合并的数据集包含该字段。
• 主查询部分：仿照sale字段的统计逻辑，新增对应每个表的该字段求和列，命名规则保持和原有统计列一致（比如table1_profit）。

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