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

select city, driver_id, avg_grade, avg_order_num, avg_mileage
from
(select city, driver_id, avg_grade, avg_order_num, avg_mileage, rank()over(partition by city order by avg_grade desc) as rk
from
(select city, driver_id, round(avg(grade),1) as avg_grade, round(count(tb_get_car_order.order_id)/count(distinct date(order_time)),1) as avg_order_num, round(sum(mileage)/count(distinct date(order_time)),3) as avg_mileage
from tb_get_car_record join tb_get_car_order on tb_get_car_record.order_id = tb_get_car_order.order_id
group by city, driver_id) as t1) as t2
where rk = 1
order by avg_order_num