over - row number column mysql

Con MySQL, ¿cómo puedo generar una columna que contenga el índice de registro en una tabla?

Question

¿Hay alguna forma de que pueda obtener el número de fila real de una consulta?

Quiero poder ordenar una tabla llamada league_girl por un campo llamado score; y devuelve el nombre de usuario y la posición de fila real de ese nombre de usuario.

Quiero clasificar a los usuarios para poder decir dónde está un usuario en particular, es decir. Joe está en la posición 100 de 200, es decir,

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

He visto algunas soluciones aquí, pero he probado la mayoría de ellas y ninguna devuelve realmente el número de fila.

He intentado esto:

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

Como derivado

... pero parece que no devuelve la posición de la fila.

¿Algunas ideas?

Answer 1

Aquí viene la estructura de la plantilla que utilicé:

  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

Y aquí está mi código de trabajo:

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

Answer 2

Es posible que desee probar lo siguiente:

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

La parte JOIN (SELECT @curRow := 0) permite la inicialización de la variable sin requerir un comando SET separado.

Caso de prueba:

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);

Consulta de prueba:

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;

Resultado:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)

Answer 3

Sé que el OP está pidiendo una respuesta de mysql pero como encontré que las otras respuestas no funcionan para mí,

• La mayoría de ellos falla con el order by
• O simplemente son muy poco eficientes y hacen que su consulta sea muy lenta para una tabla de grasa

Así que para ahorrar tiempo para otros como yo, simplemente indexe la fila después de recuperarlos de databse

ejemplo en PHP:

        $users = UserRepository::loadAllUsersAndSortByScore();

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

ejemplo en PHP usando desplazamiento y límite para paginación:

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

        $users = UserRepository::loadAllUsersAndSortByScore();

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

Answer 4

Si solo quiere saber la posición de un usuario específico después del pedido por puntaje de campo, simplemente puede seleccionar todas las filas de su tabla donde el puntaje de campo es más alto que el puntaje actual del usuario. Y use el número de fila devuelto +1 para saber qué posición ocupa este usuario actual.

Suponiendo que su tabla es "league_girl" y su campo principal es "id", puede usar esto:

SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>

Answer 5

Suponiendo que MySQL lo admite, puede hacer esto fácilmente con una subconsulta SQL estándar:

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;

Para grandes cantidades de resultados mostrados, esto será un poco lento y querrás cambiar a una auto unión en su lugar.

Answer 6

También puedes usar

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

para inicializar la variable de contador.

Answer 7

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo