webentwicklung-frage-antwort-db.com.de

Wie kann ich mit MySQL eine Spalte erstellen, die den Datensatzindex in einer Tabelle enthält?

Gibt es eine Möglichkeit, die tatsächliche Zeilennummer von einer Abfrage zu erhalten?

Ich möchte in der Lage sein, eine Tabelle namens league_girl nach einem Feld namens score zu bestellen. und geben Sie den Benutzernamen und die tatsächliche Zeilenposition dieses Benutzernamens zurück.

Ich möchte die Benutzer in eine Rangfolge bringen, damit ich feststellen kann, wo sich ein bestimmter Benutzer befindet. Joe ist Position 100 von 200, d.h.

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

Ich habe hier ein paar Lösungen gesehen, aber ich habe die meisten probiert und keine von ihnen liefert tatsächlich die Zeilennummer.

Ich habe das ausprobiert:

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

Wie abgeleitet 

... aber es scheint nicht die Zeilenposition zurückzugeben.

Irgendwelche Ideen?

mysqlindexing
97
TheBounder

Möglicherweise möchten Sie Folgendes versuchen:

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

Der JOIN (SELECT @curRow := 0)-Teil ermöglicht die Initialisierung der Variablen, ohne dass ein separater SET-Befehl erforderlich ist.

Testfall:

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

Testanfrage:

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;

Ergebnis:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)
164
Daniel Vassallo
SELECT @i:[email protected]+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo
37
Peter Johnson

Hier ist die Struktur der Vorlage, die ich verwendet habe:

  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

Und hier ist mein Arbeitscode:

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
goadreamer

Sie können auch verwenden

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

um die Zählervariable zu initialisieren.

4
Hearth

Wenn Sie nur die Position eines bestimmten Benutzers nach der Reihenfolge anhand der Feldbewertung erfahren möchten, können Sie einfach alle Zeilen aus Ihrer Tabelle auswählen, deren Feldbewertung über der aktuellen Benutzerbewertung liegt. Verwenden Sie die zurückgegebene Zeilennummer + 1, um die Position dieses aktuellen Benutzers zu ermitteln.

Wenn Sie davon ausgehen, dass Ihr Tisch "league_girl" ist und Ihr Hauptfeld "id" ist, können Sie Folgendes verwenden:

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

Wenn MySQL dies unterstützt, können Sie dies leicht mit einer Standard-SQL-Unterabfrage tun:

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;

Bei großen Mengen angezeigter Ergebnisse ist dies etwas zu langsam und Sie möchten stattdessen zu einem Self Join wechseln.

3
ftzdomino

Ich weiß, dass das OP nach einer mysql-Antwort fragt, aber da ich die anderen Antworten gefunden habe, die für mich nicht funktionieren, 

  • Die meisten von ihnen scheitern mit order by 
  • Oder sie sind einfach sehr unwirksam und machen Ihre Abfrage für eine fette Tabelle sehr langsam

Um Zeit für andere wie mich zu sparen, indizieren Sie die Zeile einfach nach dem Abrufen aus der Datenbank

beispiel in PHP:

        $users = UserRepository::loadAllUsersAndSortByScore();

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

beispiel in PHP mit Offset und Limit für 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
azerafati

Ich fand die ursprüngliche Antwort unglaublich hilfreich, wollte aber auch eine bestimmte Anzahl von Zeilen anhand der von mir eingefügten Zeilennummern verwenden. Daher verpackte ich die gesamte ursprüngliche Antwort in einer Unterabfrage, sodass ich auf die Zeilennummer verweisen konnte, die ich eingefügt habe.

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;

Eine Unterabfrage in einer Unterabfrage ist nicht sehr effizient. Es lohnt sich also zu testen, ob Sie ein besseres Ergebnis erzielen, wenn Ihr SQL-Server diese Abfrage verarbeitet, oder ob Sie die gesamte Tabelle abrufen und die Zeilen die Anwendung/den Web-Server nachträglich bearbeiten lassen .

Persönlich ist mein SQL-Server nicht überlastet, daher wäre es besser, die verschachtelten Unterabfragen zu bearbeiten.

0
BasicExp