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

#过了 但是感觉好冗杂

with T3 as
(select T1.city,T1.driver_id,ant2
from (
        select city,max(ant1) ant2
        from (select city,driver_id,round(avg(grade),1) ant1
        from tb_get_car_record t1 join tb_get_car_order t2 using(uid,order_id)
        group by city,driver_id)as T1
        group by city)as T2,

        (select city,driver_id,round(avg(grade),1) ant1
        from tb_get_car_record t1 join tb_get_car_order t2 using(uid,order_id)
        group by city,driver_id)as T1

where T2.city=T1.city and T2.ant2=T1.ant1)

select T3.city,T3.driver_id,T3.ant2,T5.avg2,T5.sum2
from T3 join(
select driver_id,round(avg(avg1),1) avg2,round(avg(sum1),3) sum2
from(
select T3.driver_id,count(*) avg1,sum(ifnull(mileage,0)) sum1
from tb_get_car_record t1 join tb_get_car_order t2 using(uid,order_id),T3
where t2.driver_id=T3.driver_id
group by T3.driver_id,date_format(t1.event_time,'%Y-%m-%d'))as T4
group by driver_id)as T5 using(driver_id)
order by T5.avg2 asc