朋友们，俺指定是写复杂了 第一步t1： 计算所有城市的最高评分，用于后面过滤筛选 重点是测试天津的时候，有一天没有里程，却不能为null，因为avg会出错

t1 as
(select 
city,max(avg_grade) as max_grade
 from 
(select 
b.city,a.driver_id,avg(a.grade) as avg_grade
from 
tb_get_car_order a
left join tb_get_car_record b using(order_id)
group by 1,2) a
group by 1)
,
t2 as 
(select b.city,date_format(a.order_time,'%Y%m%d'),a.driver_id,
 ifnull(count(a.order_time),0) as order_num,
 ifnull(sum(mileage),0) as mileage_num
 from tb_get_car_order a
 left join tb_get_car_record b using(order_id)
 group by 1,2,3
)
,
t3 as 
(select city,driver_id,avg(order_num) as avg_order_num,avg(mileage_num) as avg_mileage
 from t2
 group by 1,2
)
, 
t4 as 
(select 
b.city,a.driver_id,avg(a.grade) as avg_grade
from 
tb_get_car_order a
left join tb_get_car_record b using(order_id)
group by 1,2)

select 
t3.city,t3.driver_id,round(t4.avg_grade,1),round(t3.avg_order_num,1),
round(t3.avg_mileage,3)
from 
t3
left join t4 using(driver_id)
where (t3.city,t4.avg_grade) in  (select * from t1)
order by 4 asc