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

select if(grouping(driver_id)=0,driver_id,'总体'),
round(avg(grade),1)
from tb_get_car_order 
where grade is not null -- 2、有评价的订单
and 
driver_id in (select distinct driver_id from tb_get_car_order where left(date(order_time),7)='2021-10' and start_time is null) -- 1、2021-10月中有过取消订单的司机
group by driver_id with rollup

1、思路

一、首先找到2021-10月中有过取消订单的司机

SELECT DISTINCT driver_id

FROM tb_get_car_order

WHERE left(date(order_time),7)='2021-10' and start_time is null

二、找到这些司机的所有有评价的订单

即 tb_get_car_order 中 driver_id in（）且 grade is not null

三、对这些订单根据司机求平均分

2、函数使用

group by xxx with rollup和grouping