Mysql从group by中减去两行值

Question

我使用以下查询来获取两行收入和扣除额的结果。

select t2.type
     , sum(t1.amount) as total_earnings 
  from employee_pay_elements t1
  join pay_elements t2 
    on (t1.pay_element_id = t2.id)
 group 
    by t2.type

和结果

现在我需要获取差异(收入 - 扣除)，如何轻松高效地获取差异？

提前谢谢

Answer 1

您可以使用条件聚合:

select sum(case when pe.type = 'EARNING' then epe.amount
                when pe.type = 'DEDUCTION' then - epe.amount
            end) as total_earnings
from employee_pay_elements epe join
     pay_elements pe
     on epe.pay_element_id = pe.id;