mysql - 您如何将两个求和查询合并为一个？

SELECT dep.depName, SUM(worker.salary) as total
FROM team, worker
WHERE worker.depID = dep.depID
GROUP BY dep.depID

SELECT dep.depName, SUM(manager.salary) as total
FROM manager, dep
WHERE manager.depID = dep.depID
GROUP BY team.depID

我尝试了以下方法：SELECT dep.depName，总计SUM（manager.salary）+ SUM（worker.salary），但它给了我一些奇怪的输出，它增加了N个元素N次。

最佳答案

更有效的方法是按部门汇总结果，而不进行联接。然后加入部门名称。

此方法还允许您将部门薪金分为两组：

select depId, sum(wsalary) as wsalary, sum(msalary) as msalary,
       sum(wsalary) + sum(msalary) as total
from ((select depId, sum(w.salary) as wsalary, null as msalary
       from worker w
       group by depId
      )
      union all
      (select depId, NULL as wsalary, sum(m.salary) as msalary
       from manager m
       group by depId
      )
     ) t join
     dep d
     on t.depId = d.depId
group by t.depId

我假设第一个team子句中的from确实引用了dep，在其余的查询中也是如此。