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

select c.courier_id,c.courier_name,
sum(base_salary)+sum(delivery_fee)-sum(expense_amount) total_income
from couriers_info c
join (select courier_id ,sum(delivery_fee) delivery_fee
from deliveries_info
where delivery_date  >= '2024-07-01' AND delivery_date < '2024-08-01'
group by courier_id ) t1 on t1.courier_id =c.courier_id 
join (select courier_id,sum(expense_amount) expense_amount
from expenses_info
where expense_date >= '2024-07-01' AND expense_date < '2024-08-01'
group by courier_id) t2 on t2.courier_id =c.courier_id 
group by c.courier_id,c.courier_name
order by c.courier_id

不使用子查询 直接表关联会导致笛卡尔积