Comment récupérer efficacement la position d'une ligne dans une clause MySQL ORDER BY ?

Obtenir efficacement la position de la ligne dans la clause MySQL ORDER BY

Le tri des données dans les tables de la base de données fournit une vue structurée des enregistrements. Cependant, il est parfois nécessaire de déterminer la position d'une ligne spécifique dans les résultats triés. Cet article explique comment y parvenir à l'aide de la clause ORDER BY dans MySQL et fournit une solution optimisée pour récupérer une seule ligne et son emplacement.

Énoncé du problème :

Considérons une table MySQL nommée "tbl" qui contient deux colonnes : "id" et "name". Lorsqu'elles sont triées par la colonne "nom" par ordre croissant, les données du tableau ressemblent à ceci :

<code>+-----------+--------------------+
| id         | name               |
+-----------+--------------------+
| 5          | Alpha              |
| 7          | Beta               |
| 3          | Delta              |
| ...        | ...                |
| 1          | Zed                |
+-----------+--------------------+</code>

Cible :

Le but est de récupérer la ligne "Beta" de la table triée et sa position dans le jeu de résultats. Le résultat souhaité doit être :

<code>+-----------+-------------+--------------+
| id         | position    | name        |
+-----------+-------------+--------------+
| 7          |             2 | Beta        |
+-----------+-------------+--------------+</code>

Option 1 : Requête optimisée

Pour récupérer la position d'une ligne spécifique dans la clause ORDER BY, nous pouvons utiliser une sous-requête pour attribuer des numéros de ligne de manière incrémentielle en fonction de l'ordre de tri. La requête suivante y parvient efficacement :

<code class="language-sql">SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM tbl t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'</code>

Dans cette requête, nous créons une sous-requête (entre parenthèses) qui utilise une variable définie par l'utilisateur (@rownum) pour attribuer des numéros de ligne incrémentiels. Faites un JOIN avec une table virtuelle initialisée avec @rownum := 0, en vous assurant que les nombres commencent à 1. La clause WHERE filtre les résultats pour récupérer uniquement les lignes portant le nom « Beta ».

Option 2 : Requête alternative

La requête alternative suivante peut être formulée pour obtenir la localisation :

<code class="language-sql">SELECT t.id,
       (SELECT COUNT(*)
          FROM tbl x
         WHERE x.name < t.name) + 1 AS position,
       t.name
  FROM tbl t
 WHERE t.name = 'Beta';</code>

Contrairement au premier scénario, cette requête calcule la position en comptant le nombre de lignes portant le même nom ou moins que le nom cible. Cependant, il est important de noter que cette approche peut entraîner la duplication de lignes portant le même nom, en leur attribuant la même valeur de position.

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!