如何对三个表的指定列求和并区分数据来源表？

核心思路是在每个子查询里新增一个字段标记数据的来源表，再将这个标记字段加入分组条件，就能按客户+来源表分别计算求和结果，同时明确每条数据对应的来源。

修改后的SQL语句如下：

select 
    CustName, CustNumber, cost, table_source, sum(QUANTITY) as total 
from 
    (select CustName, CustNumber, cost, QUANTITY, 'table1' as table_source from table1 
     union all
     select CustName, CustNumber, cost, QUANTITY, 'table2' as table_source from table2 
     union all
     select CustName, CustNumber, cost, QUANTITY, 'table3' as table_source from table3) x
WHERE CustNumber = '894047001034' AND CustName = 'Joseph Smythe'
group by CustName, CustNumber, cost, table_source;

关键修改点说明 #

• 每个子查询新增'表名' as table_source字段，用固定字符串标记该条数据的来源表
• group by子句加入table_source，确保按客户信息+来源表分组求和，结果里每条记录都会对应具体的来源表
• 把CustNumber和cost也加入group by（部分SQL模式要求非聚合列必须出现在group by中，避免语法报错）

如果需要同时查看各表单独求和以及所有表的总合计，可以用ROLLUP扩展分组：

select 
    CustName, CustNumber, cost, 
    case when grouping(table_source) = 1 then '总合计' else table_source end as table_source,
    sum(QUANTITY) as total 
from 
    (select CustName, CustNumber, cost, QUANTITY, 'table1' as table_source from table1 
     union all
     select CustName, CustNumber, cost, QUANTITY, 'table2' as table_source from table2 
     union all
     select CustName, CustNumber, cost, QUANTITY, 'table3' as table_source from table3) x
WHERE CustNumber = '894047001034' AND CustName = 'Joseph Smythe'
group by CustName, CustNumber, cost, rollup(table_source);

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