mysql - 将两个 Select Sum 语句合并为一个

Question

我的表中有两个语句，它们单独工作得很好，如下所示:

SELECT fee_earner, (SUM(fe_fees)) AS Total
FROM fees
GROUP BY fee_earner
order by Total desc;

SELECT supervisor, (SUM(sv_fees)) AS Total
FROM fees
GROUP BY supervisor
order by Total desc;

但有些情况下，fee_earner和supervisor字段的数据是同一个人，有没有办法将这两个语句合二为一，得到总体总数？

Answer 1

您可以使用union all来实现:

SELECT person, sum(fe_fees) as fe_fees, sum(sv_fees) as sv_fees,
       (sum(fe_fees) + sum(sv_fees)) as total
FROM ((select fee_earner as person, fe_fees as fe_fees, 0 as sv_fees, 'earner' as which
       from fees
      ) union all
      (select supervisor as person, 0 as fe_fees, sv_fees as sv_fees, 'supervisor' as which
       from fees
      )
     ) t
GROUP BY person
order by Total desc;