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

with t1 as (
    select courier_id,
           sum(delivery_fee) as 'total_delivery_fee'
    from deliveries_info
    where delivery_date between '2024-07-01' and '2024-07-31'
    group by courier_id
),
t2 as (
    select courier_id,
        sum(expense_amount) as 'total_expense_amount'
    from expenses_info
    where expense_date between '2024-07-01' and '2024-07-31'
    group by courier_id
)
select ci.courier_id,
       ci.courier_name,
       ci.base_salary + t1.total_delivery_fee - t2.total_expense_amount as 'total_income'
from couriers_info ci
         inner join t1 on ci.courier_id = t1.courier_id
         inner join t2 on ci.courier_id = t2.courier_id

先单独算出来这个快递员需要加的运送费，需要扣去的费用，最后在做连接