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

select
t1.courier_id,
t1.courier_name,
t1.base_salary+fee1-fee2 as total_income
from couriers_info t1
left join (
    select
    courier_id,
    sum(delivery_fee) as fee1
    from deliveries_info
    where delivery_date between '2024-07-01' and '2024-07-31'
    group by 1
) t2 on t1.courier_id = t2.courier_id
left join (
    select
    courier_id,
    sum(expense_amount) as fee2
    from expenses_info
    where expense_date between '2024-07-01' and '2024-07-31'
    group by 1
) t3 on t1.courier_id = t3.courier_id
order by 1;