Umbuzo we-SQL ukumisela uluhlu lodidi08
14

Namhlanje ndifumene lo msebenzi ulandelayo ngelixa ndisebenza kwiprojekthi yabathengi: Thatha iitafile ze-SQL "umsebenzisi" kunye neekholamu "id" kunye "namanqaku" kunye nokuchonga uluhlu lwabo bonke abasebenzisi abalungiswe "ngamanqaku" apho abasebenzisi fumana inqanaba elifanayo ngamanqaku afanayo. Lo msebenzi unokusonjululwa ngokulula kwaye ngokulula ngokusebenzisa izinto ezichaziweyo zomsebenzisi .

---

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)

Sebenzisa izinto eziguquguqukayo ezimbini ukusombulula ngokulula

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

kwaye ufumana imveliso oyifunayo

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