分组Top N 问题

今天面试，面试官给了这样一个场景：
有两张表，一张表存放 车队ID，班组ID，司机ID
另一种表存放 司机ID，运营时间，运营里程

要查询出 7月份每个车队每个班组里的 Top 3

这就要用到 ROW_NUMBER() 函数

首先按需求建两张表

CREATE TABLE demo_of_topn_car
(
companyid varchar(8),
classid varchar(8),
driverid varchar(8)
)

CREATE TABLE demo_of_topn_operating
(
driverid varchar(8),
operadate datetime,
mileage DECIMAL(5,2)
)

第一步思路 先把每个车队每个班组每个司机的运营总和计算出来

SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from
(
SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid
) car
LEFT JOIN
(
SELECT a.companyid,a.classid,a.driverid,b.mileage 
FROM demo_of_topn_car a
LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid
) operat ON car.companyid = operat.companyid AND car.classid = operat.classid
GROUP BY car.companyid,car.classid,operat.driverid

然后对结果集进行分组排序

SELECT companyid,classid,driverid,row_number() over (PARTITION by companyid,classid ORDER BY mileage desc) rid from (
SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from
(
SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid
) car
LEFT JOIN
(
SELECT a.companyid,a.classid,a.driverid,b.mileage 
FROM demo_of_topn_car a
LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid
) operat ON car.companyid = operat.companyid AND car.classid = operat.classid
GROUP BY car.companyid,car.classid,operat.driverid
ORDER BY mileage DESC
) t1 

切记：求 Top N 都是倒序

最后需要在外面嵌套一层，或者用with

with res as (
SELECT companyid,classid,driverid,row_number() over (PARTITION by companyid,classid ORDER BY mileage desc) rid from (
SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from
(
SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid
) car
LEFT JOIN
(
SELECT a.companyid,a.classid,a.driverid,b.mileage 
FROM demo_of_topn_car a
LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid
) operat ON car.companyid = operat.companyid AND car.classid = operat.classid
GROUP BY car.companyid,car.classid,operat.driverid
ORDER BY mileage DESC
) t1 )  

SELECT * from res where rid <= 3

不知道大家有什么更好的办法，欢迎指导。 ：）