题解 | #每个城市中评分最高的司机信息#
每个城市中评分最高的司机信息
https://www.nowcoder.com/practice/dcc4adafd0fe41b5b2fc03ad6a4ac686
select city,driver_id,round(avg_grade,1) as avg_grade, round(avg_order_num,1) as avg_order_num, round(avg_mileage,3) as avg_mileage from( select *,rank()over(partition by city order by avg_grade desc) as rk from( select city,driver_id,avg(grade) as avg_grade, count(order_id)/count(distinct date_format(order_time,'%Y-%m-%d')) as avg_order_num, sum(mileage)/count(distinct date_format(order_time,'%Y-%m-%d')) as avg_mileage from tb_get_car_order join tb_get_car_record using(order_id) group by city,driver_id) as t1) as t2 where rk = 1 order by avg_order_num
按照城市和出租车师傅的编号进行分组聚合,并且统计所需统计量,之后进行排序,找到每个城市第一的相关信息,最后按照题目要求输出即可