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

【思路一】

分别统计不同的结果，然后将结果union，但是不知道为啥只能过一半的实例【？？？？】

以下是错误写法！！！

with tb as
(select
    driver_id
    ,grade
from tb_get_car_order
where date_format(order_time,'%Y-%m') = '2021-10' and driver_id in (select driver_id from tb_get_car_order where fare is null) and grade is not null
) 

(select
    driver_id
    ,round(avg(grade),1) as avg_grade
from tb
group by driver_id
order by driver_id
)
union all
(select 
    '总体' as driver_id
    ,round(avg(grade),1) as avg_grade
from tb   
)     
;

【错误原因分析】

题目要求计算的对象是10月份有取消订单的司机，但是driver_id in (select driver_id from tb_get_car_order where fare is null)计算的司机不一定是10月份取消订单的，应该将时间限制在括号里面。

【正确代码】

with tb as
(select
    driver_id
    ,grade
from tb_get_car_order
where driver_id in (select driver_id from tb_get_car_order where fare is null and date_format(order_time,'%Y-%m') = '2021-10') and grade is not null
) 

(select
    driver_id
    ,round(avg(grade),1) as avg_grade
from tb
group by driver_id
order by driver_id
)
union all
(select 
    '总体' as driver_id
    ,round(avg(grade),1) as avg_grade
from tb   
)     
;

【思路二】

对以上代码进行优化，使用rollup函数

with tb as
(select
    driver_id
    ,grade
from tb_get_car_order
where driver_id in (select driver_id from tb_get_car_order where fare is null and date_format(order_time,'%Y-%m') = '2021-10') and grade is not null
) 

select
    ifnull(driver_id,'总体')
    ,round(avg(grade),1) as avg_grade
from tb
group by driver_id with rollup 
;