题解 | SQL 175. 有取消订单记录的司机平均评分

WITH t1 AS ( -- 1、先找出谁是被取消的司机
    SELECT
        driver_id
    FROM tb_get_car_order
    WHERE DATE(order_time) BETWEEN '2021-10-01'
    AND '2021-10-07'
    AND start_time is NULL
    GROUP BY driver_id
),
t2 AS (
    SELECT
        driver_id,
        ROUND(AVG(grade),1) AS avg_grade
    FROM tb_get_car_order
    WHERE start_time is not NULL
    AND driver_id IN
        (SELECT t1.driver_id FROM t1)
    GROUP BY driver_id
    ORDER BY driver_id ASC
),
t3 AS (
    SELECT '总体' AS driver_id,
           ROUND(AVG(grade),1) AS avg_grade
    FROM tb_get_car_order
    WHERE start_time is not null
    AND driver_id IN
        (SELECT driver_id FROM t1)
)
SELECT * FROM t2
UNION
SELECT * FROM t3;

# 这道题的关键在于先判断driver_id所属日期范围，再进行全局计算！