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

WITH driver_tb AS(
    SELECT DISTINCT driver_id 
    FROM tb_get_car_order 
    WHERE fare IS NULL
    AND DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-31' #记得转换order_time格式，否则会遗漏最后一天
) #筛选有取消订单司机
SELECT COALESCE(driver_id,'总体') driver_id, ROUND(AVG(grade),1) avg_grade #使用COALESCE而不是IFNULL保证通用性
FROM tb_get_car_order
WHERE driver_id IN (SELECT * FROM driver_tb)
GROUP BY driver_id WITH ROLLUP #汇总
ORDER BY GROUPING(driver_id) ASC, driver_id ASC #记得把汇总加入排序