就按照题目一步一步的来,先算平均数,在排序,将rank为1的联解 思路二可以使用max函数,然后用in
select city,tmp.driver_id,round(mean,1),
round(count(order_id)/count(distinct left(ord.order_time,10)),1) as n,
round(sum(mileage)/count(distinct left(ord.order_time,10)),3)
from
( select city,driver_id,mean,
(rank() over(partition by city
order by mean desc)) as rk
from( select city,driver_id,avg(grade) as mean
from tb_get_car_record re right join tb_get_car_order ord using(order_id,uid)
group by city,driver_id) base) tmp
right join tb_get_car_order ord using(driver_id)
where rk=1
group by city,tmp.driver_id,mean
order by n