题解 | 物流公司想要分析快递小哥的薪资构成和绩效情况

select c.courier_id
, courier_name
, sum(base_salary+delivery_fee-expense_amount) total_income
from couriers_info c
left join 
    (select courier_id,sum(delivery_fee) delivery_fee from deliveries_info
    where month(delivery_date) = 7 group by 1)d 
on c.courier_id = d.courier_id
left join 
    (select courier_id,sum(expense_amount) expense_amount from expenses_info
    where month(expense_date) = 7 group by 1) e 
on c.courier_id = e.courier_id
group by 1,2