MySQL SELECT 与 GROUP BY 一个字段和 ORDER BY 另一字段

Question

编辑 3 - MySQL 版本是 8.0.33。

编辑 2 - 请参阅底部的最终工作代码。谢谢@Akina！

我有一个体育赛事的得分表。该表具有我要选择的三个相关字段 -

1. scoreID 作为主键值
2. classifierID 映射到另一个表的主键，该表包含有关特定课程布局的详细信息
3. calculatedPercent 是特定事件的结果

该表还有我在 WHERE 子句中使用的其他三个字段，但这些字段是偶然的。

我需要生成一个查询，为 calculatedPercent 选择四个最佳值，并规定 classifierID 不能重复。我需要能够捕获 scoreID 以便在流程的后续阶段使用。

这是我的第一个查询：

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

最初我认为这很棒，因为它确实为给定成员具有最高 calculatedPercent 值的行选择了 scoreID 值。然后我注意到有几个成员在同一门课程上获得了第一和第二高分，这违反了 classifierID 值不重复的要求。

我尝试了一下 SELECT DISTINCT，但最终意识到我真正需要的是 GROUP BY，所以我做了一些研究，发现在 MySql 中执行查询时出现与 only_full_group_by 相关的错误，但这并没有完全解决我的问题。

我接下来尝试了：

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

这是以下错误消息：

#1055 - ORDER BY 子句的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“.masterScores.calculatedPercent”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

我考虑对 masterScores.scoreID 列使用 MIN 和 MAX，但它与预期不符； scoreID 主键值并不总是所选 calculatedPercent 的值。我在某处读到，因为 scoreID 是主键，所以我可以通过使用 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

乍一看，这似乎确实有效，但它并没有始终返回与 bestPercent 值匹配的 scoreID 值。

再次强调，目标是：

1. 根据指定的 WHERE 子句，仅为每个 classifierID 选择 1 个 calculatedPercent 和 1 个 scoreID 值。如果不按 classifierID 分组，则每个 classifierID 可能有 0 到 400 行满足 WHERE 子句，因此我认为 GROUP BY 在这里是合适的。

2. 确保为每个分组的 classifierID 所选的 calculatedPercent 是所有选项中最高的数值

3. 确保仅选择 4 行，并且这些行是所选 calculatedPercent 值最高的行。

4. 确保所选的 4 行根据 calculatedPercent 值按降序排列。

5. 确保每个选定行的 scoreID 值实际上代表与选定的 calculatedPercent 相同的行（目前，这是计算百分比的点）我的查询失败）。

以下是数据子集，例如：

分数ID	分类器ID	最佳百分比
58007	42	66.60
63882	42	64.69
64685	54	64.31
58533	32	63.20
55867	42	62.28
66649	7	56.79
55392	12	50.28
58226	1	49.52
55349	7	41.10

这是我运行查询时所需的输出：

分数ID	分类器ID	最佳百分比
58007	42	66.60
64685	54	64.31
58533	32	63.20
66649	7	56.79

这是我运行查询时的实际输出：

分数ID	分类器ID	最佳百分比
55867	42	66.60
64685	54	64.31
58533	32	63.20
55349	7	56.79

如图所示，实际输出第一行和第四行的 scoreID 值不正确。

目前，我欢迎任何建议。

编辑 2 - 最终工作解决方案

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

我能够针对六个出现问题的案例对此进行测试，并且该解决方案解决了每个问题。再次感谢@Akina！

将标记此问题已解决。

Answer 1

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

如果(classifierID,calculatedPercent) 不唯一，那么每个classifierID 可能会收到多行。在这种情况下，您需要

SELECT MAX(t1.scoreID) AS scoreID, classifierID, calculatedPercent AS bestPercent 
FROM masterScores t1
NATURAL JOIN (
    SELECT classifierID, MAX(calculatedPercent) AS calculatedPercent
    FROM masterScores t2
    WHERE memberID = 3516 AND eventDivision = "O" AND scoreUnusable != "TRUE" 
    GROUP BY 1
    ORDER BY calculatedPercent DESC LIMIT 4
    ) t2
GROUP BY 2, 3

PS。如果您的 MySQL 版本为 8+，则必须在 CTE 中使用 ROW_NUMBER() 而不是子查询。

---

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 
)
SELECT scoreID, classifierID, bestPercent
FROM cte
WHERE rn = 1