MySQL 8.0.33 分组筛选不重复分类的最高得分记录并匹配对应主键的查询问题

大家好，我最近在处理赛事得分数据时遇到了一个棘手的查询问题，折腾了好几种方法都没搞定，最后在@Akina的帮助下解决了，这里把过程和方案分享给大家。

问题背景与需求 #

我有一张赛事得分表masterScores，核心字段包括：

• scoreID：主键，每条得分记录的唯一标识
• classifierID：关联赛道表的外键，代表不同的赛道布局
• calculatedPercent：赛事得分的百分比结果
  还有memberID、eventDivision、scoreUnusable这三个字段用来做筛选条件。

我的核心需求是：

• 筛选出指定用户（memberID=3516）、指定组别（eventDivision="O"）且有效（scoreUnusable!="TRUE"）的得分记录
• 每个classifierID只能保留最高得分的那条记录，不能重复
• 最终取top4的最高得分记录，按calculatedPercent降序排列
• 必须保证返回的scoreID和对应最高得分的记录是同一行（这是我之前踩坑的关键点）

尝试过的方法与遇到的坑 #

1. 简单排序+LIMIT

一开始我直接用排序加限制条数的写法：

SELECT `masterScores`.`scoreID`, `masterScores`.`classifierID`, `masterScores`.`calculatedPercent`
FROM `masterScores`
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE"
ORDER BY `masterScores`.`calculatedPercent` DESC LIMIT 4

看起来没问题，但很快发现一个问题：有些用户的前几名得分来自同一个赛道，导致classifierID重复，不符合“每个赛道只取一条”的要求。

2. GROUP BY+MAX聚合

意识到需要按赛道分组后，我尝试用GROUP BY配合MAX来取每个赛道的最高得分：

SELECT `masterScores`.`scoreID`, `masterScores`.`classifierID`, MAX(`masterScores`.`calculatedPercent`) AS bestPercent
FROM `masterScores`
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE"
GROUP BY `masterScores`.`classifierID`
ORDER BY bestPercent DESC LIMIT 4

结果直接触发了only_full_group_by的错误：

#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '.masterScores.calculatedPercent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3. 用ANY_VALUE绕过限制

后来看到有人说可以用ANY_VALUE来处理非聚合列，于是修改了查询：

SELECT ANY_VALUE(`masterScores`.`scoreID`), `masterScores`.`classifierID`, MAX(`masterScores`.`calculatedPercent`) AS bestPercent
FROM `masterScores`
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE"
GROUP BY `masterScores`.`classifierID`
ORDER BY bestPercent DESC LIMIT 4

这次没有报错，但结果不对——返回的scoreID经常和最高得分的记录不匹配，比如示例数据里，赛道42的最高得分是66.60，对应的scoreID应该是58007，但查询返回的却是55867，完全不是同一行记录。

最终解决方案（CTE+窗口函数） #

在@Akina的提示下，我用MySQL 8.0支持的窗口函数ROW_NUMBER()解决了这个问题，思路是：

1. 用CTE（公共表表达式）给每个赛道的记录按得分降序排号，得分相同的话按scoreID降序（确保取最新的记录）
2. 只保留每个赛道排号为1的记录（也就是该赛道的最高得分记录）
3. 最后对这些记录按得分降序取top4

完整代码如下：

WITH cte AS (
SELECT scoreID, classifierID, calculatedPercent AS bestPercent,
ROW_NUMBER() OVER (PARTITION BY classifierID ORDER BY calculatedPercent DESC, scoreID DESC) AS rn
FROM masterScores WHERE memberID = 3516 AND eventDivision = "O" AND scoreUnusable != "TRUE"
)
SELECT scoreID, classifierID, bestPercent
FROM cte
WHERE rn = 1
ORDER BY bestPercent DESC
LIMIT 4

我用了几个有问题的测试案例验证，这个查询完美解决了所有问题：每个赛道只取最高得分的记录，scoreID完全匹配，最终返回的top4记录也符合预期。

备注：内容来源于stack exchange，提问作者Eric Brockway