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

select a.courier_id, courier_name,
sum(base_salary+delivery_fee-expense_amount) as total_income
from couriers_info a
join (
    select courier_id, sum(delivery_fee) as delivery_fee
    from deliveries_info
    where year(delivery_date)=2024 and month(delivery_date)=7
    group by courier_id
) b on a.courier_id=b.courier_id
join (
    select courier_id, sum(expense_amount) as expense_amount
    from expenses_info
    where year(expense_date)=2024 and month(expense_date)=7
    group by courier_id
) c on b.courier_id=c.courier_id
group by a.courier_id, courier_name
order by a.courier_id