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

就按照题目一步一步的来，先算平均数，在排序，将rank为1的联解 思路二可以使用max函数，然后用in

select city,tmp.driver_id,round(mean,1),
       round(count(order_id)/count(distinct left(ord.order_time,10)),1) as n,
       round(sum(mileage)/count(distinct left(ord.order_time,10)),3)
from 
( select city,driver_id,mean,
       (rank() over(partition by city
                             order by mean desc)) as rk
from( select city,driver_id,avg(grade) as mean
from tb_get_car_record re right join tb_get_car_order ord using(order_id,uid)
group by city,driver_id) base) tmp 
right join tb_get_car_order ord using(driver_id)
where rk=1
group by city,tmp.driver_id,mean
order by n