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

select
    c.courier_id as courier_id,
    courier_name,
    base_salary + delivery_total - expense_total as total_income
from
    couriers_info as c
    inner join (
        select
            courier_id,
            sum(delivery_fee) as delivery_total
        from
            deliveries_info
        where
            substr(delivery_date, 1, 7) = '2024-07'
        group by
            courier_id
    ) as d on c.courier_id = d.courier_id
    inner join (
        select
            courier_id,
            sum(expense_amount) as expense_total
        from
            expenses_info
        where
            substr(expense_date, 1, 7) = '2024-07'
        group by
            courier_id
    ) as e on c.courier_id = e.courier_id;