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

SELECT
	city,
	driver_id,
	avg_grade,
	avg_order_num,
	avg_mileage 
FROM
	(
	SELECT
		tr.city,
		bo.driver_id,
		round( sum( bo.grade ) / count( bo.grade ), 1 ) avg_grade,
		round(
			count( date_format( bo.order_time, "%Y-%m-%d" ) ) / count( DISTINCT date_format( bo.order_time, "%Y-%m-%d" ) ),
			1 
		) avg_order_num,
		round( sum( bo.mileage ) / count( DISTINCT date_format( bo.order_time, "%Y-%m-%d" ) ), 3 ) avg_mileage 
	FROM
		tb_get_car_order bo
		INNER JOIN tb_get_car_record tr ON bo.order_id = tr.order_id 
	GROUP BY
		bo.driver_id,
		tr.city 
	) M
	INNER JOIN (
	SELECT
		max( N.avg_grade ) mn,
		N.city mag 
	FROM
		(
		SELECT
			tr.city,
			round( sum( bo.grade ) / count( bo.grade ), 1 ) avg_grade 
		FROM
			tb_get_car_order bo
			INNER JOIN tb_get_car_record tr ON bo.order_id = tr.order_id 
		GROUP BY
			tr.city,
			bo.driver_id 
		) N 
	GROUP BY
		city 
	) T ON M.city = T.mag 
	AND M.avg_grade = T.mn
	ORDER BY avg_mileage