Update der Rang in einer MySQL-Tabelle

Ich habe folgende Tabellenstruktur für Tabelle Spieler 

Table Player {  
Long playerID;  
Long points;  
Long rank;  
}

Unter der Annahme, dass die playerID und die Punkte haben gültige Werte, kann ich ein update der Rang für alle die Spieler basierend auf der Anzahl der Punkte in einer einzigen Abfrage? Wenn zwei Personen die gleiche Anzahl von Punkten, sollten Sie die Krawatte für den Rang.

UPDATE:

Ich bin mit hibernate über die query vorgeschlagen, als eine native Abfrage. Hibernate nicht wie die Verwendung von Variablen, insbesondere die ':'. Kennt jemand irgendwelche workarounds? Entweder durch nicht verwenden von Variablen oder arbeiten rund um hibernate ist eine Einschränkung in diesem Fall durch die Verwendung von HQL?

  • In Bezug auf Ihre Bearbeitung, auf hibernate, möchten Sie vielleicht post eine neue Frage, wie es bekommen mehr Aufmerksamkeit.
InformationsquelleAutor smahesh | 2010-04-28
  1. 16

    Eine Möglichkeit ist die Verwendung einer ranking-variable, wie die folgenden:

    UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

    Den JOIN (SELECT @curRank := 0) Teil ermöglicht die Initialisierung der Variablen, ohne dass ein separates SET Befehl.

    Weitere Lektüre zu diesem Thema:

    Test-Fall:

    CREATE TABLE player (
   playerID int,
   points int,
   rank int
);

INSERT INTO player VALUES (1, 150, NULL);
INSERT INTO player VALUES (2, 100, NULL);
INSERT INTO player VALUES (3, 250, NULL);
INSERT INTO player VALUES (4, 200, NULL);
INSERT INTO player VALUES (5, 175, NULL);

UPDATE   player
JOIN     (SELECT    p.playerID,
                    @curRank := @curRank + 1 AS rank
          FROM      player p
          JOIN      (SELECT @curRank := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

    Ergebnis:

    SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
5 rows in set (0.00 sec)

    UPDATE: ist Nur aufgefallen, das, die Sie benötigen Krawatten den gleichen Rang. Das ist ein bisschen tricky, aber kann gelöst werden, mit noch mehr Variablen:

    UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

    Für einen Testfall, fügen wir anderen Spieler mit 175 Punkten:

    INSERT INTO player VALUES (6, 175, NULL);

    Ergebnis:

    SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    4 |
|        2 |    100 |    5 |
+----------+--------+------+
6 rows in set (0.00 sec)

    Und wenn Sie den Rang zu überspringen, ein Ort im Falle eines Unentschiedens, können Sie weitere hinzufügen IF Zustand:

    UPDATE   player
JOIN     (SELECT    p.playerID,
                    IF(@lastPoint <> p.points, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    IF(@lastPoint = p.points, 
                       @curRank := @curRank + 1, 
                       @curRank),
                    @lastPoint := p.points
          FROM      player p
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
          ORDER BY  p.points DESC
         ) ranks ON (ranks.playerID = player.playerID)
SET      player.rank = ranks.rank;

    Ergebnis:

    SELECT * FROM player ORDER BY rank;

+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
|        3 |    250 |    1 |
|        4 |    200 |    2 |
|        5 |    175 |    3 |
|        6 |    175 |    3 |
|        1 |    150 |    5 |
|        2 |    100 |    6 |
+----------+--------+------+
6 rows in set (0.00 sec)

    Hinweis: Bitte beachten Sie, dass die Abfragen, die ich Vorschlage, könnte weiter vereinfacht werden.

    • vielen Dank, das ist genau das, was ich brauchte. Danke für die links.
    • Daniel, bitte siehe mein Kommentar zu meiner eigenen Antwort.
    InformationsquelleAutor Daniel Vassallo
  2. 6

    Daniel, du hast sehr schöne Lösung. Außer einem Punkt - die Band-Fall. Wenn Band geschieht zwischen 3-Spieler dieses update funktioniert nicht richtig. Ich änderte Ihre Lösung wie folgt:

    UPDATE player  
    JOIN (SELECT p.playerID,  
                 IF(@lastPoint <> p.points,  
                    @curRank := @curRank + @nextrank,  
                    @curRank)  AS rank,  
                 IF(@lastPoint = p.points,  
                    @nextrank := @nextrank + 1,  
                    @nextrank := 1),  
                 @lastPoint := p.points  
            FROM player p  
            JOIN (SELECT @curRank := 0, @lastPoint := 0, @nextrank := 1) r  
           ORDER BY  p.points DESC  
          ) ranks ON (ranks.playerID = player.playerID)  
SET player.rank = ranks.rank;
    InformationsquelleAutor Giannis Lazaridis
  3. 3

    EDIT: Die update-Anweisung, die weiter oben dargestellt hast, nicht funktionieren.

    Obwohl dies nicht genau das, was Sie fordern: Sie erzeugen den Rang on-the-fly bei der Auswahl:

    select p1.playerID, p1.points, (1 + (
    select count(playerID) 
      from Player p2 
     where p2.points > p1.points
    )) as rank
from Player p1
order by points desc

    EDIT: Versucht die UPDATE-Anweisung einmal mehr. Wie wäre es mit einer temporären Tabelle:

    create temporary table PlayerRank
    as select p1.playerID, (1 + (select count(playerID) 
                                   from Player p2 
                                  where p2.points > p1.points
              )) as rank
         from Player p1;

update Player p set rank = (select rank from PlayerRank r 
                             where r.playerID = p.playerID);

drop table PlayerRank;

    Hoffe, das hilft.

    • Nein, es wird nicht funktionieren. Sie bekommen würde, eine You can't specify target table 'p1' for update in FROM clause, weil der p1 Referenz in der subquery.
    • Vielen Dank für die Klarstellung Daniel. Seit Col. Granatsplitter wies darauf hin, dass, streng genommen, Rang sollte berechnet werden, bei wählen Sie die Zeit, lassen Sie mich darauf hinweisen, dass mein untergeordneten select sollte funktionieren für diesen Zweck.
    • Ja, das Unterabfrage funktionieren würde bei SELECT Zeit, aber es wird noch nicht mit Krawatten. Die OP selbst tagged die Frage "Unentschieden"! 🙂
    • Ich habe eine modifizierte version des query-as-update Spieler set g1 g1.Rang = 1 + ((SELECT count(*) from (select * from player) g2, wobei g2.Punkte > g1.Punkte)) ich Frage mich, ob es irgendeine Optimierung, die getan werden kann, auf es.
    • Ich sehe nicht, warum meine Aussage nicht behandeln Krawatten. Jeder Spieler die gleiche Punktzahl erhalten den gleichen Rang. Wenn zwei Spieler Unentschieden für Rang 5, der nächste Spieler(s) zugewiesen Rang 7. Wenn das nicht wie "Umgang mit Beziehungen" funktionieren soll, wir haben ein unterschiedliches Verständnis von dem Begriff.
    • Und hat deine Anweisung arbeiten? Ist es langsam oder warum willst du es optimieren?
    • Es funktioniert in der Tat. Ich habe die Abfrage falsch interpretiert. +1 weil es eine gute option. Aber ich denke, Sie sollten, Bearbeiten Sie die Antwort, denn es ist zu einem Fehler führt, wie es jetzt ist. Die select-subqery gut funktioniert: SELECT p1.points, p1.playerID, 1 + (select count(playerID) FROM Player p2 WHERE p2.points > p1.points) rank FROM player p1 ORDER BY rank;
    • Recht hast du. Ich bearbeitet meine Antwort. Vielen Dank für die +1.
    • meine Aussage funktioniert, aber es ist langsam, wie es dauert, exponentielle Zeit, und ich wollte sehen, ob es war ein Weg, um es schneller zu laufen.
    • ist dort Weg, um konvertieren Sie Ihre select-Klausel in einer update-Anweisung für die gesamte Tabelle. Ich habe Schwierigkeiten, weil von " can 'T specify target table for update in FROM-Klausel' ...
    • Finden Sie andere Bearbeiten.
    • vielen Dank für diese Anregung, ich werde das ausprobieren.

    InformationsquelleAutor Tom Bartel
  4. 0

    Laut Normalisierungsregeln, Rang sollte bewertet werden WÄHLEN Sie Zeit.

    • Ja, aber das ist in Erster Linie eine lookup-Tabelle, wobei der Rang berechnet wird, die periodisch ist und ich nicht jedes mal ein Benutzer anmeldet.
    InformationsquelleAutor Your Common Sense
Schreibe einen Kommentar