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

SELECT c.courier_id,courier_name,
    base_salary + total_fee - total_cost AS total_income
FROM couriers_info c
JOIN (
    SELECT courier_id, SUM(delivery_fee) AS total_fee
    FROM deliveries_info
    WHERE delivery_date BETWEEN '2024-07-01' AND '2024-07-31'
    GROUP BY courier_id) a
USING(courier_id)
JOIN(
    SELECT courier_id, SUM(expense_amount) AS total_cost
    FROM expenses_info
    WHERE expense_date BETWEEN '2024-07-01' AND '2024-07-31'
    GROUP BY courier_id) b
USING(courier_id)