MySQL SELECT mit GROUP BY in einem Feld und ORDER BY in einem anderen Feld

Question

Edit 3 – MySQL-Version ist 8.0.33.

Edit 2 – Siehe endgültigen Arbeitscode unten. Danke @Akina!

Ich habe einen Spielberichtsbogen für eine Sportveranstaltung. Die Tabelle enthält drei verwandte Felder, die ich auswählen möchte -

1. scoreID als Primärschlüsselwert
2. classifierID Wird einem Primärschlüssel einer anderen Tabelle zugeordnet, die Details zu einem bestimmten Kurslayout enthält
3. calculatedPercent ist das Ergebnis eines bestimmten Ereignisses

Die Tabelle enthält drei weitere Felder, die ich in der WHERE-Klausel verwende, aber diese sind nebensächlich.

Ich muss eine Abfrage generieren, die die vier besten Werte für calculatedPercent 选择四个最佳值，并规定 classifierID 不能重复。我需要能够捕获 scoreID auswählt und angibt, dass

nicht wiederholt werden kann. Ich muss in der Lage sein,

für die Verwendung in späteren Phasen des Prozesses zu erfassen.

Das ist meine erste Anfrage: calculatedPercent 值的行选择了 scoreID 值。然后我注意到有几个成员在同一门课程上获得了第一和第二高分，这违反了 classifierID

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

Anfangs fand ich das großartig, weil es die Zeile mit dem höchsten

Wert für ein bestimmtes Mitglied auswählt. Dann ist mir aufgefallen, dass mehrere Mitglieder im selben Kurs die erste und zweithöchste Note hatten, was gegen die

-Anforderung der Nichtduplizierung von Werten verstieß.

Ich habe SELECT DISTINCT ausprobiert, aber schließlich wurde mir klar, dass ich wirklich GROUP BY brauchte, also habe ich etwas recherchiert und festgestellt, dass ich beim Ausführen von Abfragen in MySql Fehler im Zusammenhang mit only_full_group_by bekam, aber das hat mein Problem nicht vollständig gelöst.

Was ich als nächstes ausprobiert habe:

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

Dies ist die folgende Fehlermeldung:

#1055 – Ausdruck Nr. 1 der ORDER BY-Klausel ist nicht in der GROUP BY-Klausel und enthält die nicht aggregierte Spalte „.masterScores.calculatedPercent“, die funktional nicht von den Spalten in der GROUP BY-Klausel abhängt; dies ist inkonsistent mit sql_mode=only_full_group_by ist nicht kompatibelmasterScores.scoreID 列使用 MIN 和 MAX，但它与预期不符； scoreID 主键值并不总是所选 calculatedPercent 的值。我在某处读到，因为 scoreID

Ich habe überlegt, MIN und MAX für die Spalte masterScores.scoreID zu verwenden, aber es funktioniert nicht wie erwartet

der Primärschlüsselwert ist nicht immer der ausgewählte

Wert. Ich habe irgendwo gelesen, dass ich das Problem mithilfe der ANY_VALUE-Aggregation beheben kann, da

der Primärschlüssel ist. Ich habe Folgendes versucht:

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

1. Auf den ersten Blick scheint dies zu funktionieren, aber es wird nicht immer ein

   -Wert zurückgegeben, der mit dem bestPercent-Wert übereinstimmt. classifierID 选择 1 个 calculatedPercent 和 1 个 scoreID 值。如果不按 classifierID 分组，则每个 classifierID

   Auch hier ist das Ziel:

2. classifierID 所选的 calculatedPercentWählt nur 1

   und 1
Wert für jeden
3. basierend auf der angegebenen WHERE-Klausel aus. Ohne Gruppierung nach

   könnte jedes calculatedPercent zwischen 0 und 400 Zeilen haben, die die WHERE-Klausel erfüllen, daher denke ich, dass GROUP BY hier angemessen wäre.

4. Stellen Sie sicher, dass der für jede Gruppe ausgewählte calculatedPercent der höchste Wert unter allen Optionen ist

   🎜 🎜🎜Stellen Sie sicher, dass nur 4 Zeilen ausgewählt sind und dass dies die Zeilen mit dem höchsten 🎜 ausgewählten Wert sind. 🎜 🎜 🎜🎜Stellen Sie sicher, dass die 4 ausgewählten Zeilen in absteigender Reihenfolge basierend auf dem 🎜-Wert sortiert sind. 🎜

5. Stellt scoreID 值实际上代表与选定的 calculatedPercent die gleiche Zeile für jede ausgewählte Zeile sicher (derzeit ist dies der Punkt, an dem der Prozentsatz berechnet wird und meine Abfrage fehlschlägt).

Das Folgende ist beispielsweise eine Teilmenge der Daten:

Score-ID	Klassifikator-ID	Bester Prozentsatz
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

Dies ist die gewünschte Ausgabe, wenn ich die Abfrage ausführe:

Score-ID	Klassifikator-ID	Bester Prozentsatz
58007	42	66,60
64685	54	64,31
58533	32	63,20
66649	7	56,79

Dies ist die tatsächliche Ausgabe, wenn ich die Abfrage ausführe:

Score-ID	Klassifikator-ID	Bester Prozentsatz
55867	42	66,60
64685	54	64,31
58533	32	63,20
55349	7	56,79

Wie in der Abbildung gezeigt, sind die scoreID-Werte der ersten und vierten Zeile der tatsächlichen Ausgabe falsch.

Im Moment freue ich mich über alle Vorschläge.

Edit 2 – Endgültige Arbeitslösung

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

Ich konnte dies anhand von sechs Fällen testen, in denen das Problem auftrat, und die Lösung hat jedes Problem behoben. Nochmals vielen Dank @Akina!

Dieses Problem wird als gelöst markiert.

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