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

# 思路：
# 1、将订单表中有取消记录的司机的订单记录提取出来
# 2、计算全体平均数，driver_id设为总体，形成表1
# 3、计算每个司机平均评分，形成表1
# 4、将表1 和表2union连接

select t2.driver_id,round(AVG(t2.grade),1) as avg_grade from (
# 将有取消记录的司机的订单记录提取
select c1.driver_id,c1.grade from tb_get_car_order as c1 
where c1.driver_id in 
(select c2.driver_id from tb_get_car_order as c2 
where c2.start_time is null )) t2 
group by t2.driver_id 
union 
select '总体' as driver_id,round(AVG(t1.grade),1) as avg_grade from (
# 将有取消记录的司机的订单记录提取
select c1.driver_id,c1.grade from tb_get_car_order as c1 
where c1.driver_id in 
(select c2.driver_id from tb_get_car_order as c2 
where c2.start_time is null )) t1