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

select
    t1.courier_id courier_id,
    t1.courier_name courier_name,
    t1.base_salary+t2.s_delivery_fee-t3.s_expense_amount total_income
from(
    select * from couriers_info
) t1
join
(
    select courier_id, sum(delivery_fee) s_delivery_fee
    from deliveries_info
    where delivery_date between '2024-07-01' and '2024-07-31'
    group by courier_id
) t2
on t1.courier_id = t2.courier_id
join
(
    select courier_id, sum(expense_amount) s_expense_amount
    from expenses_info
    where expense_date between '2024-07-01' and '2024-07-31'
    group by courier_id   
) t3
on t1.courier_id = t3.courier_id
group by t1.courier_id