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

SELECT CI.courier_id,
       courier_name,
       base_salary + delivery_fee_total - expense_amount_total AS total_income
FROM couriers_info AS CI
JOIN  (SELECT DI.courier_id,
             SUM(delivery_fee) AS delivery_fee_total
      FROM deliveries_info AS DI 
      WHERE date_format(delivery_date,'%Y-%m') = '2024-07' 
      GROUP BY DI.courier_id
     ) AS A ON A.courier_id = CI.courier_id
JOIN (SELECT EI.courier_id,
             SUM(expense_amount) AS expense_amount_total
     FROM  expenses_info AS EI 
     WHERE date_format(expense_date,'%Y-%m') = '2024-07' 
     GROUP BY EI.courier_id
    ) AS B ON B.courier_id = CI.courier_id
ORDER BY CI.courier_id