Comment puis-je récupérer efficacement la valeur maximale et la valeur associée dans SQL avec le regroupement ?

Une solution élégante pour obtenir efficacement la valeur maximale et ses valeurs associées dans le regroupement SQL

En SQL, il est souvent délicat de trouver la valeur maximale dans une autre colonne et d'obtenir sa valeur associée en fonction de la troisième colonne après regroupement. Considérez le scénario suivant :

Les formulaires suivants sont disponibles :

KEY	NUM	VAL
A	1	AB
B	1	CD
B	2	EF
C	2	GH
C	3	HI
D	1	JK
D	3	LM

Le but est de trouver la valeur VAL correspondant au NUM maximum pour chaque KEY. Les résultats attendus sont les suivants :

KEY	VAL
A	AB
B	EF
C	HI
D	LM

Bien que la requête suivante puisse être utilisée pour atteindre l'objectif :

<code class="language-sql">select KEY, VAL
from TABLE_NAME TN
where NUM = (
    select max(NUM)
    from TABLE_NAME TMP
    where TMP.KEY = TN.KEY
    )</code>

Mais quand il y a beaucoup de valeurs KEY, cette méthode semble maladroite. Cependant, utiliser la fonction row_number() offre une solution plus élégante :

<code class="language-sql">select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
      from table_name t
     ) t
where seqnum = 1;</code>

Cette méthode utilise les concepts de partitionnement et de tri pour obtenir les résultats souhaités. En définissant la partition de row_number() sur KEY et en triant par NUM dans l'ordre décroissant, chaque ligne de chaque partition se voit attribuer un numéro de séquence. Sélectionnez ensuite la ligne portant le numéro de série 1 pour obtenir la valeur NUM maximale correspondant à chaque KEY et sa valeur VAL associée.

Il est important de noter que cette approche améliorée se comporte légèrement différemment de la requête d'origine. La requête d'origine peut renvoyer plusieurs lignes pour chaque KEY, tandis que cette solution garantit qu'une seule ligne par KEY est renvoyée. Si vous devez conserver le comportement d'origine, vous pouvez remplacer dense_rank() par rank() ou row_number().

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!