SQL-query om een ​​ranglijst te bepalen

Vandaag kwam ik de volgende taak tegen tijdens het werken aan een klantproject: Neem een ​​SQL-tabel "user" met de kolommen "id" en "score" en bepaal een ranglijst van alle gebruikers gesorteerd op "score" waarin gebruikers met de dezelfde ranking krijgen met dezelfde score. Deze taak kan intuïtief en eenvoudig worden opgelost door gebruik te maken van door de gebruiker gedefinieerde variabelen .


CREATE TABLE
    user(id INT, score INT);
INSERT INTO
    user(id, score)
VALUES
    (1, 10), (2, 40), (3, 55), (4, 10), (5, 5), (6, 20), (7, 30), (8, 70), (9, 30)

Het gebruik van twee variabelen lost dit eenvoudig op

SET @score_prev = NULL;
SET @cur_rank = 0;
SELECT id, score, CASE
    WHEN @score_prev = score THEN @cur_rank
    WHEN @score_prev := score THEN @cur_rank := @cur_rank + 1
END AS rank
FROM user
ORDER BY score DESC

en je krijgt de output die je wilt

+--------+-----------+----------+
|  *id*  |  *score*  |  *rank*  |
|    8   |      70   |      1   |
|    3   |      55   |      2   |
|    2   |      40   |      3   |
|    7   |      30   |      4   |
|    9   |      30   |      4   |
|    6   |      20   |      5   |
|    1   |      10   |      6   |
|    4   |      10   |      6   |
|    5   |       5   |      7   |
+--------+-----------+----------+
Terug