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

-- 逻辑拆解：时间条件-2024年7月，求——总收入，直接连接表分组聚合就可以(难点：避开笛卡尔积)
WITH total_income AS(
    SELECT 
        t1.courier_id,
        t1.courier_name,
        ROUND(t1.base_salary+SUM(t2.delivery_fee),2) total_income
    FROM couriers_info t1
    LEFT JOIN deliveries_info t2
    ON t1.courier_id = t2.courier_id
    AND t2.delivery_date BETWEEN '2024-07-01' AND '2024-07-31'
    GROUP BY t1.courier_id,t1.courier_name
),
total_expense AS(
    SELECT 
        t1.courier_id,
        t1.courier_name,
        ROUND(SUM(t2.expense_amount),2) total_expense
    FROM couriers_info t1
    LEFT JOIN expenses_info t2
    ON t1.courier_id = t2.courier_id
    AND t2.expense_date BETWEEN '2024-07-01' AND '2024-07-31'
    GROUP BY t1.courier_id,t1.courier_name   
)
SELECT 
    t1.courier_id,
    t1.courier_name,
    t1.total_income - t2.total_expense total_income
FROM total_income t1
LEFT JOIN total_expense t2
ON t1.courier_id = t2.courier_id
ORDER BY t1.courier_id