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

--拆解写法，容易理解，因为连表会产生很多条基础工资，题目我们只提取一个一次基础工资，所以比如全部算完在连接，这样把每个人的工资增加或者扣除，就只有一条个人数据。

with tiaojian as (
select 
courier_id,
sum(delivery_fee) as ps
from deliveries_info
where
date_format(delivery_date,"%Y%m")=202407
group by courier_id
),tiaojian1 as (
select 
courier_id,
sum(expense_amount) as zc
from expenses_info
where
date_format(expense_date,"%Y%m")=202407
group by courier_id
)


select 
cf.courier_id,
cf.courier_name,
cf.base_salary+t.ps-t1.zc as total_income
from couriers_info cf inner join tiaojian t 
on cf.courier_id=t.courier_id
inner join tiaojian1 t1 on t1.courier_id=cf.courier_id
order by cf.courier_id