通过连接两个表汇总数据的SQL查询修正求助

你当前的SQL语句先执行了TableA和TableB的JOIN操作，这会产生笛卡尔积：同一个Job下，TableA的每一条记录都会和TableB的每一条记录匹配。比如Job=1时，TableA有2条记录、TableB有3条，连接后会生成6条记录，此时再用SUM计算就会把Cost重复累加3次、Sell重复累加2次，最终得到错误的汇总值。

正确的做法是先分别对两个表按Job分组汇总，再将汇总后的结果进行关联，以此避免笛卡尔积带来的重复计算：

SELECT 
    COALESCE(a.Job, b.Job) AS Job,
    a.TotalCost AS Cost,
    b.TotalSell AS Sell
FROM 
    (SELECT Job, SUM(Cost) AS TotalCost FROM TableA GROUP BY Job) a
FULL OUTER JOIN 
    (SELECT Job, SUM(Sell) AS TotalSell FROM TableB GROUP BY Job) b
ON 
    a.Job = b.Job;

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用UNION ALL结合分组实现兼容：

SELECT 
    Job,
    SUM(TotalCost) AS Cost,
    SUM(TotalSell) AS Sell
FROM 
    (SELECT Job, SUM(Cost) AS TotalCost, 0 AS TotalSell FROM TableA GROUP BY Job
     UNION ALL
     SELECT Job, 0 AS TotalCost, SUM(Sell) AS TotalSell FROM TableB GROUP BY Job) t
GROUP BY Job;

执行修正后的语句后，会得到你期望的结果：

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