题解 | #每个城市中评分最高的司机信息#

select city,driver_id,round(avg_grade,1) as avg_grade,
       round(avg_order_num,1) as avg_order_num,
       round(avg_mileage,3) as avg_mileage
from(
select *,rank()over(partition by city order by avg_grade desc) as rk
from(
select city,driver_id,avg(grade) as avg_grade,
count(order_id)/count(distinct date_format(order_time,'%Y-%m-%d')) as avg_order_num,
sum(mileage)/count(distinct date_format(order_time,'%Y-%m-%d')) as avg_mileage
from tb_get_car_order
join tb_get_car_record
using(order_id)
group by city,driver_id) as t1) as t2
where rk = 1
order by avg_order_num

按照城市和出租车师傅的编号进行分组聚合，并且统计所需统计量，之后进行排序，找到每个城市第一的相关信息，最后按照题目要求输出即可