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