如何使用T-SQL查询每年总积分最高的车手姓名及对应最大积分值

问题原因 #

原代码未先按车手+年度聚合累计积分，直接基于单场比赛积分排序，因此只能得到单场最高分的条目。

正确实现代码 #

WITH yearly_total AS
(
   -- 第一步：统计每个车手每年度的累计总积分
   SELECT
     CONCAT(d.forename,' ',d.surname) AS driver_name,
     rc.year,
     SUM(CONVERT(FLOAT, r.points)) AS total_points
   FROM 
     results AS r    
     INNER JOIN constructors AS c ON r.constructorId = c.constructorId
     INNER JOIN drivers AS d ON r.driverId = d.driverId
     INNER JOIN races AS rc ON r.raceId = rc.raceId
   GROUP BY CONCAT(d.forename,' ',d.surname), rc.year
),
ranked_drivers AS
(
   -- 第二步：对每年度的车手按累计积分倒序排名
   SELECT 
     driver_name,
     year,
     total_points AS max_points,
     ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_points DESC) AS rn
     -- 若需要展示同一年度积分并列第一的所有车手，将上方ROW_NUMBER替换为RANK()即可
   FROM yearly_total
)
-- 取每年度排名第一的记录
SELECT driver_name, year, max_points 
FROM ranked_drivers 
WHERE rn = 1

逻辑说明 #

1. 第一层CTE yearly_total 先按车手姓名、年度分组，对每场比赛的积分求和，得到每个车手每年度的总积分
2. 第二层CTE ranked_drivers 以年度为分区，按车手年度总积分倒序排名
3. 最后筛选出每年度排名第一的记录，即可得到期望的年度积分最高车手及对应总积分

内容的提问来源于stack exchange，提问作者Meital Meyer