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

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

group by c.courier_id, courier_name
order by c.courier_id