MySQL如何实现两张关联表的双SUM分组求和查询？

注意：不要直接关联两张表后执行SUM运算，否则会产生笛卡尔积导致统计值翻倍，同个idUnit在两张表各有N、M条记录时，聚合结果会是正确值的M、N倍，不符合需求。

正确实现逻辑为：先分别对两张表按idUnit分组求和得到各自的统计结果，再通过idUnit字段关联两个统计结果集即可。

基础写法（覆盖所有在Tabel_1中出现过的idUnit） #

SELECT 
  t1.idUnit,
  t1.TotalBudget,
  COALESCE(t2.TotalReal2, 0) AS TotalReal2
FROM (
  -- 先统计Tabel_1每个idUnit的总预算
  SELECT idUnit, SUM(Budget) AS TotalBudget
  FROM Tabel_1
  GROUP BY idUnit
) t1
LEFT JOIN (
  -- 再统计Tabel_2每个idUnit的总实际值
  SELECT idUnit, SUM(Real2) AS TotalReal2
  FROM Tabel_2
  GROUP BY idUnit
) t2 ON t1.idUnit = t2.idUnit;

兼容写法（覆盖两张表中所有出现过的idUnit） #

如果需要包含仅在Tabel_2中存在的idUnit，MySQL不支持全外连接，可以用UNION ALL合并后二次聚合的方式实现：

SELECT 
  idUnit,
  SUM(TotalBudget) AS TotalBudget,
  SUM(TotalReal2) AS TotalReal2
FROM (
  SELECT idUnit, SUM(Budget) AS TotalBudget, 0 AS TotalReal2
  FROM Tabel_1
  GROUP BY idUnit
  UNION ALL
  SELECT idUnit, 0 AS TotalBudget, SUM(Real2) AS TotalReal2
  FROM Tabel_2
  GROUP BY idUnit
) t
GROUP BY idUnit;

以上两种写法针对你提供的测试数据，都会返回idUnit=112、TotalBudget=3000、TotalReal2=600的预期结果。

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