Jak za pomocą MySQL mogę wygenerować kolumnę zawierającą indeks rekordów w tabeli?

Czy istnieje sposób, aby uzyskać rzeczywisty numer wiersza z zapytania?

Chcę mieć możliwość zamówienia tabeli o nazwie league_girl za pomocą pola o nazwie score; i zwróć nazwę użytkownika i rzeczywistą pozycję w wierszu tej nazwy użytkownika.

Chcę uszeregować użytkowników, aby móc określić, gdzie jest dany użytkownik, tj. Joe zajmuje pozycję 100 z 200, tj

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

Widziałem tutaj kilka rozwiązań, ale wypróbowałem większość z nich i żadne z nich nie zwraca numeru wiersza.

Próbowałem tego:

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

Jak pochodne

... ale wygląda na to, że nie zwraca pozycji wiersza.

Jakieś pomysły?

Możesz spróbować następujących rzeczy:

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

JOIN (SELECT @curRow := 0)Część pozwala zmiennej inicjalizacji bez potrzeby dodatkowego SETpolecenia.

Przypadek testowy:

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

Zapytanie testowe:

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;

Wynik:

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

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

Oto struktura szablonu, którego użyłem:

  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

A oto mój działający kod:

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

Możesz także użyć

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

aby zainicjować zmienną licznika.

Zakładając, że MySQL to obsługuje, możesz to łatwo zrobić za pomocą standardowego podzapytania SQL:

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;

W przypadku dużej liczby wyświetlanych wyników będzie to trochę powolne i zamiast tego będziesz chciał przełączyć się na łączenie własne.

Jeśli chcesz tylko poznać pozycję jednego konkretnego użytkownika po uporządkowaniu według wyniku pola, możesz po prostu wybrać wszystkie wiersze z tabeli, w których wynik pola jest wyższy niż bieżący wynik użytkownika. I użyj numeru wiersza zwróconego + 1, aby dowiedzieć się, która pozycja bieżącego użytkownika.

Zakładając, że Twoja tabela jest league_girli Twoje podstawowe pole to id, możesz użyć tego:

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

Oryginalna odpowiedź okazała się niezwykle pomocna, ale chciałem też pobrać pewien zestaw wierszy na podstawie numerów wierszy, które wstawiałem. W związku z tym zawarłem całą pierwotną odpowiedź w podzapytaniu, aby móc odwołać się do numeru wiersza, który wstawiałem.

SELECT * FROM 
( 
    SELECT *, @curRow := @curRow + 1 AS "row_number"
    FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;

Posiadanie podzapytania w podzapytaniu nie jest zbyt wydajne, dlatego warto sprawdzić, czy uzyskasz lepszy wynik, zlecając serwerowi SQL obsługę tego zapytania, czy pobierając całą tabelę i zmuszając aplikację / serwer WWW do manipulowania wierszami po fakcie .

Osobiście mój serwer SQL nie jest zbyt zajęty, więc preferowane było posiadanie obsługi zagnieżdżonych podzapytań.

Wiem, że OP prosi o mysqlodpowiedź, ale ponieważ okazało się, że inne odpowiedzi nie działają dla mnie,

• Większości z nich się to nie udaje order by
• Lub są po prostu bardzo nieefektywne i powodują, że zapytanie jest bardzo wolne w przypadku grubej tabeli

Aby zaoszczędzić czas innym takim jak ja, po prostu zindeksuj wiersz po pobraniu ich z bazy danych

przykład w PHP:

$users = UserRepository::loadAllUsersAndSortByScore();

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

przykład w PHP z użyciem offsetu i limitu stronicowania:

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

$users = UserRepository::loadAllUsersAndSortByScore();

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