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

#筛选这些司机已完成订单切有评分的订单信息，与总体平均评分合并表
(
select
driver_id
,round(avg(grade),1)
from tb_get_car_order tgco left join tb_get_car_record tgcr
on tgco.order_id = tgcr.order_id
where grade is not null 
and driver_id in 
(#找到有过取消订单记录的司机
    select
    distinct
    driver_id
    from tb_get_car_order
    where date_format(order_time,'%Y%m') = '202110'
    and start_time is null
)
group by 1
order by 1
)
union all
(
select
'总体' driver_id
,round(avg(grade),1)
from tb_get_car_order tgco left join tb_get_car_record tgcr
on tgco.order_id = tgcr.order_id
where  grade is not null 
and driver_id in 
(
    select
    distinct
    driver_id
    from tb_get_car_order
    where date_format(order_time,'%Y%m') = '202110'
    and start_time is null
)
)