题解 | #每个城市评分最高的司机# 涉及取最大值问题

#把城市与driver_id连接起来，之后算平均，之后取r=1

with tmp as (
select city,driver_id,
round(avg(grade),1) as avg_grade,
round(avg(mileage),3) as avg_mileage,
round(count(distinct a.order_id)/count(distinct dt),1) as avg_order_num,
dense_rank()over(partition by city order by avg(grade) desc) as r
from
(select order_id, city from tb_get_car_record) a 
inner join (
select order_id,driver_id,date_format(order_time,'%Y-%m-%d') AS dt,grade,mileage
from tb_get_car_order) b
on a.order_id=b.order_id
group by city,driver_id
)
select city,driver_id,avg_grade,avg_order_num,avg_mileage from tmp where r=1

另一种取最大值的方法

select a.city,driver_id, avg_grade,avg_order_num,avg_mileage
from tmp a
inner join 
(select city, max(avg_grade) as m from tmp group by city) b
on a.city=b.city and a.avg_grade=b.m