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

select 
    c.courier_id,
    c.courier_name,
    base_salary+sum_delivery_fee-sum_expense_amount as total_income
from couriers_info c
join 
    (select courier_id,sum(delivery_fee) as sum_delivery_fee
    from deliveries_info
    where date_format(delivery_date,'%Y-%m')='2024-07'
    group by courier_id) d 
on c.courier_id=d.courier_id
join 
    (select courier_id,sum(expense_amount) as sum_expense_amount
    from expenses_info
    where date_format(expense_date,'%Y-%m')='2024-07'
    group by courier_id) e 
on c.courier_id=e.courier_id
order by c.courier_id;