Tout à l’heure, j’ai eu à calculer la position des joueurs d’un jeu en ligne pour faire un classement des 50 meilleurs joueurs en fonction de leur score et du score des autres joueurs. Voici une technique pour y arriver facilement.

Soit une table scores qui stocke les résultats d’un jeu : le nom du joueur et son score.

mysql> DESCRIBE scores;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     |         |                |
| score | int(5) unsigned  | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Cette table contient les enregistrements suivants :

mysql> SELECT * FROM scores;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  1 | toto       |    34 |
|  2 | titi       |   456 |
|  3 | foo        |   765 |
|  4 | bar        |     3 |
|  5 | turlututu  |   987 |
|  6 | pouet      |    47 |
|  7 | prout prou |   387 |
|  8 | mouhahaha  |   193 |
|  9 | mister x   |   456 |
+----+------------+-------+
9 rows in set (0.00 sec)

On cherche a avoir le classement des joueurs comme ci dessus :

  1. turlututu
  2. foo
  3. titi
  4. mister x
  5. etc…

La premère idée serait de sélectionner les joueurs et de les trier par score, du plus grand au plus petit.

mysql> SELECT * FROM scores ORDER BY score DESC;
+----+------------+-------+
| id | name       | score |
+----+------------+-------+
|  5 | turlututu  |   987 |
|  3 | foo        |   765 |
|  2 | titi       |   456 |
|  9 | mister x   |   456 |
|  7 | prout prou |   387 |
|  8 | mouhahaha  |   193 |
|  6 | pouet      |    47 |
|  1 | toto       |    34 |
|  4 | bar        |     3 |
+----+------------+-------+
9 rows in set (0.00 sec)

Il nous suffit juste d’introduire un compteur pour trouver la position, via une variable mysql par exemple (ou en incrémentant une variable en php à l’affichage)

mysql> SET @position = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *, @position := @position + 1 AS 'position'  FROM scores ORDER BY score DESC;
+----+------------+-------+----------+
| id | name       | score | position |
+----+------------+-------+----------+
|  5 | turlututu  |   987 |        1 |
|  3 | foo        |   765 |        2 |
|  2 | titi       |   456 |        3 |
|  9 | mister x   |   456 |        4 |
|  7 | prout prou |   387 |        5 |
|  8 | mouhahaha  |   193 |        6 |
|  6 | pouet      |    47 |        7 |
|  1 | toto       |    34 |        8 |
|  4 | bar        |     3 |        9 |
+----+------------+-------+----------+
9 rows in set (0.00 sec)

Cependant cette technique n’est viable uniquement si :

  • on traite l’ensemble des enregistrements (on ne peut donc pas, par exemple, gérer un affichage par page, sauf avec de la bidouille merdique)
  • les égalités ne sont pas gérer (deux personnes ayant le même nombre de points, comme titi et mister x, n’auront pas la même place dans le classement)

Voici donc une technique simple pour gérer les positions simplement : il suffit pour chaque joueur de compter le nombre de joueurs qui ont un meilleur score qu’eux :


mysql> SELECT a.name,
    -> (SELECT COUNT(*) FROM scores AS b WHERE b.score >= a.score) AS 'position'
    -> FROM scores AS a ORDER BY position ASC;
+------------+----------+
| name       | position |
+------------+----------+
| turlututu  |        1 |
| foo        |        2 |
| titi       |        4 |
| mister x   |        4 |
| prout prou |        5 |
| mouhahaha  |        6 |
| pouet      |        7 |
| toto       |        8 |
| bar        |        9 |
+------------+----------+
9 rows in set (0.00 sec)

On y est presque, il reste juste un problème pour titi et mister x : ils sont classés tous les deux 4ème au lieu de 3ème parce qu’on compte tous les joueurs dont le score est plus grand ou égal à leur propre score.
Ce problème se résout facilement en sélectionnant de façon unique les scores supérieurs.

mysql> SELECT a.name,
    -> (SELECT COUNT(DISTINCT b.score) FROM scores AS b WHERE b.score >= a.score) AS 'position'
    -> FROM scores AS a ORDER BY position ASC;
+------------+----------+
| name       | position |
+------------+----------+
| turlututu  |        1 |
| foo        |        2 |
| titi       |        3 |
| mister x   |        3 |
| prout prou |        4 |
| mouhahaha  |        5 |
| pouet      |        6 |
| toto       |        7 |
| bar        |        8 |
+------------+----------+
9 rows in set (0.00 sec)

Voilà, on peut maintenant travailler sur un ensemble d’enregistrements et non plus forcement sur la totalité :

mysql> SELECT a.name,
    -> (SELECT COUNT(DISTINCT b.score) FROM scores AS b WHERE b.score >= a.score) AS 'position'
    -> FROM scores AS a WHERE a.name = 'pouet' ORDER BY position ASC;
+-------+----------+
| name  | position |
+-------+----------+
| pouet |        6 |
+-------+----------+
1 row in set (0.00 sec)

mysql> SELECT a.name,
    -> (SELECT COUNT(DISTINCT b.score) FROM scores AS b WHERE b.score >= a.score) AS 'position'
    -> FROM scores AS a WHERE a.score < 200 ORDER BY position ASC;
+-----------+----------+
| name      | position |
+-----------+----------+
| mouhahaha |        5 |
| pouet     |        6 |
| toto      |        7 |
| bar       |        8 |
+-----------+----------+
4 rows in set (0.01 sec)

Note: il est également possible d’utiliser une auto-jointure (utiliser deux fois la table scores) pour éviter le subselect.