Umbuzo we-SQL wokunquma uhlu lwezinga08
14

Namuhla ngihlangabezane nomsebenzi olandelayo ngenkathi ngisebenza kuphrojekthi yekhasimende: Thatha "umsebenzisi" wetafula le-SQL namakholomu "id" kanye "namaphuzu" futhi unqume uhlu lwezinga labo bonke abasebenzisi, oluhlungwe ngokusekelwe "kumaphuzu", lapho abasebenzisi abanamaphuzu afanayo bathola izinga elifanayo. Ngokusebenzisa okuguquguqukayo okuchazwe umsebenzisi, lo msebenzi ungaxazululwa ngendlela enembile futhi kalula.

---

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)

Kusetshenziswa okuguqukayo okubili kukuxazulula lokhu kalula:

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

futhi uthola umphumela owufunayo:

+--------+-----------+----------+
|  *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   |
+--------+-----------+----------+