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

按照城市和出租車師傅的編號(hào)進(jìn)行分組聚合，并且統(tǒng)計(jì)所需統(tǒng)計(jì)量，之后進(jìn)行排序，找到每個(gè)城市第一的相關(guān)信息，最后按照題目要求輸出即可