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

select 
    city   city
    ,driver_id  driver_id
    ,round(avg(grade),1)   avg_grade
    ,round(count(driver_id)/count(distinct substring(order_time,1,10)),1) avg_order_num
    ,round(sum(mileage)/count(distinct substring(order_time,1,10)),3) avg_mileage
from 
    tb_get_car_order t2
left join 
    tb_get_car_record t3 
on 
    t2.order_id=t3.order_id 
where 
    driver_id 
in 
    (select 
    driver_id 
from
(
select 
    city
    ,driver_id
    ,avg_grade 
    ,dense_rank()over(partition by city order by avg_grade desc) rk
from 
(
select
     city
    ,driver_id
    ,avg(grade) avg_grade
from 
    tb_get_car_order t4
left join 
    tb_get_car_record t5 
on 
    t4.order_id=t5.order_id
group by 
    city
    ,driver_id
) t0   
) t1 
where 
    rk=1 )
group by 
    city
    ,driver_id
order by 
    avg_order_num