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

WITH
    temp AS (
        SELECT
            courier_id,
            sum(expense_amount) AS expense
        FROM
            expenses_info
        WHERE
            MONTH(expense_date) = 7
        GROUP BY
            courier_id
    )
SELECT
    courier_id,
    courier_name,
    base_salary + fee - expense AS total_income
FROM
    (
        SELECT DISTINCT
            a.courier_id,
            a.courier_name,
            a.base_salary,
            sum(b.delivery_fee) OVER (
                PARTITION BY
                    courier_id
            ) fee,
            temp.expense
        FROM
            couriers_info AS a
            INNER JOIN deliveries_info AS b ON a.courier_id = b.courier_id
            AND MONTH(b.delivery_date) = 7
            INNER JOIN temp ON temp.courier_id = a.courier_id
        ORDER BY
            courier_id ASC
    ) AS couriers_info_2
ORDER BY
    courier_id ASC

写的有点麻烦了但思路比较简单，这道题有一个点就是不要把三个表直接相连，要不然会出现很多重复项用DISTINCT也去不掉这样的话计算总和的时候就会错误，我的处理是先将两个表相连，剩下一个表先用GROUP BY进行处理再相连，最后处理数据就好了