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

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