记录一个蠢办法

思路

1. 先计算出每个城市每个司机的平均分数，日均接单数和日均里程数
2. 计算出每个城市的最高的返
3. 利用city=city和平均分数=城市最高平均分数两个条件来连接两表（此步骤可以去掉所有平均分不是第一名的司机）
4. 再按照分数排序即可

完整代码

# 先两表连接
with tb_1 as (
    select city,
           driver_id,
           order_time,
           mileage,
           grade
    from tb_get_car_order
    join tb_get_car_record using(order_id)
),
# 每个城市中每个司机的平均评分，日均接单数，日均里程数
tb_2 as (
select city,
       driver_id,
       round(avg(grade),1) avg_grade,
       round(count(order_time)/count(DISTINCT date(order_time)),1) avg_order_num,
       round(sum(mileage)/count(DISTINCT date(order_time)),3) avg_mileage
from tb_1
group by city,driver_id
),
# 每个城市的最高平均评分
tb_3 as (select city,
       max(avg_grade) max_avg_grade
from tb_2
group by city
)

# 利用两表连接来排除不符合条件的司机，然后排序
select tb_2.*
from tb_2
join tb_3
on tb_2.city=tb_3.city
and tb_2.avg_grade = tb_3.max_avg_grade
order by tb_2.avg_order_num;