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

select ifnull(driver_id,'总体'),round(sum(grade)/count(grade),1) from tb_get_car_order as tgco where driver_id in (select driver_id from tb_get_car_order where date_format (order_time, "%Y-%m") = '2021-10'  and grade is null) group by driver_id WITH ROLLUP

• 首先挑选出订单时间是2021年10月且grade空的司机号码，grade空表示司机取消了订单

select driver_id from tb_get_car_order where date_format (order_time, "%Y-%m") = '2021-10'  and grade is null

with rollup的作用

with rollup的作用是对查询结果进行汇总统计，它会在查询结果的末尾添加一行，该行会对查询结果进行总计和汇总。

通过with rollup计算总和，作用在groupby以后

因为使用了withrollup计算groupby以后的总和所以判断driver_id是空的时候补全

ifnull(driver_id,'总体')