我的表中有两个语句,分别可以正常工作,如下所示:
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字段与数据具有相同的人员,是否有办法将这两个语句组合为一个以获取总和?
您可以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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句