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

with t as (
        select city , driver_id, avg_grade,avg_order_num, avg_mileage,rank()over(partition by city order by avg_grade desc) 'rk'

from 
(
select 
    city,
    driver_id, 
    round(avg(grade),1) 'avg_grade', 
    round(count(*) / count(distinct date_format(order_time, '%Y-%m-%d')), 1) 'avg_order_num', 
    round(sum(mileage) / count(distinct date_format(order_time, '%Y-%m-%d')), 3) 'avg_mileage'

from tb_get_car_order co
join tb_get_car_record r on r.order_id = co.order_id
group by city, driver_id
) a

)

select  city , driver_id, avg_grade,avg_order_num, avg_mileage
from t
where rk =1
order by avg_order_num asc;

• 先在子查询中使用group by city、driver_id算出需要的city , driver_id, avg_grade,avg_order_num, avg_mileage信息，然后在外循环用rk排序，注意到这里如果有同分也要一起输出，所以采用rank()