Umbuzo we-SQL ukumisela uluhlu lodidi08
14

Namhlanje ndidibene nalo msebenzi ulandelayo ngelixa ndisebenza kwiprojekthi yabathengi: Thatha itafile ye-SQL "umsebenzisi" kunye neekholamu "id" kunye "nenqaku" kwaye unqume uluhlu lwezikhundla zabo bonke abasebenzisi, abahlelwe ngokusekelwe "kwinqaku", apho abasebenzisi abanamanqaku afanayo bafumana umgangatho ofanayo. Ngokusebenzisa izinto eziguquguqukayo ezichazwe ngumsebenzisi, lo msebenzi unokusombululwa ngokufanelekileyo nangokulula.

---

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