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

-- 看问题
with t1 as (select distinct driver_id from tb_get_car_order
where mileage is null
and date(finish_time) between '2021-10-01' and '2021-10-31'
),
t2 as (select driver_id,grade
from tb_get_car_record a
left join tb_get_car_order b
on a.uid = b.uid
and a.order_id = b.order_id
where grade is not null
and start_time is not null 
)
select t1.driver_id,round(avg(grade),1) avg_grade
from t1
left join t2 
on t1.driver_id = t2.driver_id
group by driver_id
union 
select '总体' driver_id,round(avg(grade),1) avg_grade
from t1
left join t2 
on t1.driver_id = t2.driver_id