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

#查询出每个快递员在 2024 年 7 月的总收入（基本工资 + 派送费用总和 - 支出 ）。查询结果按照快递员 ID 升序排列
select distinct a.courier_id,courier_name,
        base_salary + delivery_fee - expense_amount as total_income
from couriers_info as a
inner join (
    select courier_id,sum(delivery_fee) as delivery_fee
    from deliveries_info
    where date_format(delivery_date,'%Y%m') = '202407' 
    group by courier_id
) as b
on a.courier_id = b.courier_id
inner join(
    select courier_id,sum(expense_amount) as expense_amount
    from expenses_info
    where date_format(expense_date,'%Y%m') = '202407'
    group by courier_id
) as d
on a.courier_id = d.courier_id
group by a.courier_id,courier_name
order by a.courier_id