MySQL SELECT avec GROUP BY un champ et ORDER BY un autre champ

Question

Edit 3 - La version MySQL est 8.0.33.

Edit 2 - Voir le code de travail final en bas. Merci @Akina !

J'ai une feuille de pointage pour un événement sportif. Le tableau comporte trois champs associés que je souhaite sélectionner -

1. scoreID comme valeur de clé primaire
2. classifierID Mappe vers une clé primaire d'une autre table contenant des détails sur une disposition de cours spécifique
3. calculatedPercent est le résultat d'un événement spécifique

La table contient trois autres champs que j'utilise dans la clause WHERE, mais ceux-ci sont accessoires.

Je dois générer une requête qui sélectionne les quatre meilleures valeurs pour calculatedPercent 选择四个最佳值，并规定 classifierID 不能重复。我需要能够捕获 scoreID et précise que

ne peut pas être répété. Je dois être capable de capturer

pour l'utiliser dans les étapes ultérieures du processus.

Voici ma première requête : 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

Au départ, j'ai trouvé que c'était génial car cela sélectionne la ligne avec la valeur

la plus élevée pour un membre donné. Ensuite, j'ai remarqué que plusieurs membres avaient obtenu les première et deuxième notes les plus élevées dans le même cours, ce qui violait l'exigence

de non-duplication des valeurs.

J'ai essayé SELECT DISTINCT mais j'ai finalement réalisé que ce dont j'avais vraiment besoin était GROUP BY, j'ai donc fait quelques recherches et découvert que j'obtenais des erreurs liées à only_full_group_by lors de l'exécution de requêtes dans MySql, mais cela n'a pas complètement résolu mon problème.

Ce que j'ai essayé ensuite :

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

Voici le message d'erreur suivant :

#1055 - L'expression n°1 de la clause ORDER BY n'est pas dans la clause GROUP BY et contient la colonne non agrégée ".masterScores.calculatedPercent", qui ne dépend pas fonctionnellement des colonnes de la clause GROUP BY ; avec sql_mode=only_full_group_by n'est pas compatiblemasterScores.scoreID 列使用 MIN 和 MAX，但它与预期不符； scoreID 主键值并不总是所选 calculatedPercent 的值。我在某处读到，因为 scoreID

J'ai envisagé d'utiliser MIN et MAX pour la colonne masterScores.scoreID, mais cela ne fonctionne pas comme prévu

la valeur de la clé primaire n'est pas toujours la valeur

sélectionnée ; J'ai lu quelque part que puisque

est la clé primaire, je peux résoudre ce problème en utilisant l'agrégation ANY_VALUE. J'ai essayé ceci :

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. À première vue, cela semble fonctionner, mais cela ne renvoie pas toujours une valeur

   qui correspond à la valeur bestPercent. classifierID 选择 1 个 calculatedPercent 和 1 个 scoreID 值。如果不按 classifierID 分组，则每个 classifierID

   Encore une fois, l'objectif est :

2. classifierID 所选的 calculatedPercentSélectionne seulement 1

   et 1
valeur pour chaque
3. en fonction de la clause WHERE spécifiée. Sans regroupement par

   , chaque calculatedPercent pourrait avoir entre 0 et 400 lignes satisfaisant la clause WHERE, donc je pense que GROUP BY serait approprié ici.

4. Assurez-vous que le calculatedPercent sélectionné pour chaque groupe

   est la valeur la plus élevée parmi toutes les options🎜 🎜 🎜🎜Assurez-vous que seules 4 lignes sont sélectionnées et que ce sont les lignes avec la valeur 🎜 la plus élevée sélectionnée. 🎜 🎜 🎜🎜Assurez-vous que les 4 lignes sélectionnées sont triées par ordre décroissant en fonction de la valeur 🎜. 🎜

5. Garantit scoreID 值实际上代表与选定的 calculatedPercent la même ligne pour chaque ligne sélectionnée (actuellement, c'est le point auquel le pourcentage est calculé et ma requête échoue).

Ce qui suit est un sous-ensemble des données, par exemple :

ID de score	ID du classificateur	Meilleur pourcentage
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

Voici le résultat souhaité lorsque j'exécute la requête :

ID de score	ID du classificateur	Meilleur pourcentage
58007	42	66.60
64685	54	64.31
58533	32	63.20
66649	7	56.79

Voici le résultat réel lorsque j'exécute la requête :

ID de score	ID du classificateur	Meilleur pourcentage
55867	42	66.60
64685	54	64.31
58533	32	63.20
55349	7	56.79

Comme le montre la figure, les valeurs scoreID des première et quatrième lignes de la sortie réelle sont incorrectes.

Pour l’instant, j’accepte toutes les suggestions.

Edit 2 - Solution de travail finale

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

J'ai pu tester cela sur six cas où le problème s'est produit, et la solution a résolu chaque problème. Merci encore @Akina !

Marquera ce problème comme résolu.

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

Si (classifierID,calculatedPercent) 不唯一，那么每个classifierID vous pouvez recevoir plusieurs lignes. Dans ce cas, vous avez besoin de

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. Si votre version de MySQL est 8+, vous devez utiliser ROW_NUMBER() dans le CTE au lieu d'une sous-requête.

---

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