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

SELECT
driver_id,
ROUND(SUM(grade)/COUNT(order_id), 1) AS avg_grade

FROM tb_get_car_record AS r
RIGHT JOIN tb_get_car_order AS o 
    USING(order_id)
    
WHERE 
driver_id IN (SELECT driver_id FROM tb_get_car_order WHERE start_time IS NULL AND DATE_FORMAT(order_time, '%Y-%m') = '2021-10') AND
grade IS NOT NULL

GROUP BY driver_id

UNION

SELECT
'总体' AS driver_id,
ROUND(SUM(grade)/COUNT(order_id), 1)AS avg_grade

FROM tb_get_car_record AS r
RIGHT JOIN tb_get_car_order AS o 
    USING(order_id)
    
WHERE 
driver_id IN (SELECT driver_id FROM tb_get_car_order WHERE start_time IS NULL AND DATE_FORMAT(order_time, '%Y-%m') = '2021-10') AND
grade IS NOT NULL

ORDER BY driver_id