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

SELECT *
FROM(
SELECT driver_id, ROUND(AVG(grade), 1) AS avg_grade
FROM  tb_get_car_order
WHERE driver_id IN 
(
SELECT tbo.driver_id
FROM tb_get_car_order AS tbo
INNER JOIN tb_get_car_record AS tbr
ON tbo.order_id = tbr.order_id
WHERE tbo.start_time IS NULL 
AND DATE(tbo.order_time) BETWEEN '2021-10-01' AND '2021-10-31')
GROUP BY driver_id
ORDER BY driver_id) AS t1

UNION ALL

SELECT '总体' AS driver_id, ROUND(AVG(grade), 1) AS avg_grade
FROM tb_get_car_order
WHERE driver_id IN 
(
SELECT tbo.driver_id
FROM tb_get_car_order AS tbo
INNER JOIN tb_get_car_record AS tbr
ON tbo.order_id = tbr.order_id
WHERE tbo.start_time IS NULL 
AND DATE(tbo.order_time) BETWEEN '2021-10-01' AND '2021-10-31') ;