Avec MySQL, Comment puis-je générer une colonne contenant l'index des enregistrements dans une table?

y a-t-il un moyen d'obtenir le numéro de ligne réel à partir d'une requête?

je veux pouvoir commander une table appelée league_girl par un champ appelé score; et retourner le nom d'utilisateur et la position réelle de la ligne de ce nom d'utilisateur.

je veux classer les utilisateurs pour que je puisse dire où un utilisateur particulier est, c'est à dire. Joe est en position 100 Sur 200, i.e. 

User Score Row
Joe  100    1
Bob  50     2
Bill 10     3

j'ai vu quelques solutions ici, mais j'ai essayé la plupart d'entre eux et aucun d'entre eux ne renvoie le numéro de la rangée.

j'ai essayé ceci: 

SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score 
       FROM league_girl GROUP BY username ORDER BY score DESC)

tel que dérivé

...mais il ne semble pas revenir à la position de la rangée.

des idées?

96
demandé sur einpoklum 2010-06-27 13:37:26

7 réponses

Vous pouvez essayer ce qui suit: 

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r;

la partie JOIN (SELECT @curRow := 0) permet l'initialisation de la variable sans exiger une commande séparée SET .

cas de Test: 

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);

requête de Test: 

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r
WHERE   l.score > 50;

résultat: 

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)
162
répondu Daniel Vassallo 2010-06-27 09:49:34 
SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo
36
répondu Peter Johnson 2010-08-12 17:36:03

Voici la structure du modèle que j'ai utilisé: 

  select
          /*this is a row number counter*/
          ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
          as rownumber,
          d3.*
  from 
  ( select d1.* from table_name d1 ) d3

et voici mon code de travail: 

select     
           ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
           as rownumber,
           d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
    from       maindatabase d1
    where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
    group by   YEAR( d1.date ), MONTH( d1.date ) ) d3
6
répondu goadreamer 2014-12-12 21:33:04

vous pouvez également utiliser 

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

pour initialiser la variable de compteur.

4
répondu Hearth 2013-08-21 04:37:04

en supposant que MySQL le supporte, Vous pouvez facilement le faire avec une sous-commande SQL standard: 

select 
    (count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
    username,
    score
from league_girl l2
order by score;

pour les grandes quantités de résultats affichés, ce sera un peu lent et vous voudrez passer à une auto joindre à la place.

3
répondu ftzdomino 2012-09-05 11:08:03

si vous voulez juste connaître la position d'un utilisateur spécifique après ordre Par score de champ, vous pouvez simplement sélectionner toute ligne de votre tableau où le score de champ est plus élevé que le score d'utilisateur courant. Et utilisez le numéro de ligne retourné + 1 pour savoir quelle position de cet utilisateur actuel.

en supposant que votre table est "league_girl" et que votre champ primaire est "id", vous pouvez utiliser ceci: 

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>
3
répondu Heryno 2013-10-08 06:04:58

je sais que L'OP demande une réponse mysql mais comme j'ai trouvé que les autres réponses ne fonctionnaient pas pour moi,

  • la plupart d'entre eux échouent avec order by
  • ou ils sont tout simplement très inefficace et faire votre requête très lent pour une table grasse

donc pour gagner du temps pour les autres comme moi, il suffit d'indexer la rangée après les extraire de la base de données

exemple en PHP: 

        $users = UserRepository::loadAllUsersAndSortByScore();

        foreach($users as $index=>&$user){
            $user['rank'] = $index+1;
        }

exemple en PHP utilisant offset et limit pour paging: 

        $limit = 20; //page size
        $offset = 3; //page number

        $users = UserRepository::loadAllUsersAndSortByScore();

        foreach($users as $index=>&$user){
            $user['rank'] = $index+1+($limit*($offset-1));
        }
0
répondu azerafati 2016-02-16 10:41:18