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

select a.courier_id,courier_name,
round(base_salary+sum(delivery_fee)-avg(courier_expense_amount),2) as total_income
from couriers_info a
left join deliveries_info b 
on a.courier_id = b.courier_id and delivery_date between '2024-07-01' and '2024-07-31'
left join (
    select courier_id,sum(expense_amount) as courier_expense_amount
    from expenses_info 
    where expense_date between '2024-07-01' and '2024-07-31' 
    group by courier_id ) c 
on a.courier_id = c.courier_id 
group by a.courier_id,courier_name
order by a.courier_id