Weydiinta SQL si loo go'aamiyo liiska darajaynta08
14

Maanta waxaan la kulmay hawshan soo socota markii aan ka shaqaynayay mashruuca macmiilka: Qaado miiska SQL "user" oo leh tiirarka "id" iyo "dhibcaha" oo go'aami liiska darajooyinka dhammaan isticmaalayaasha, oo loo soocay iyada oo ku saleysan "dhibcaha", taas oo isticmaalayaasha dhibcaha isku midka ah ay helaan darajo isku mid ah. Adigoo isticmaalaya doorsoomayaal ku qeexan isticmaale, hawshan waxaa lagu xallin karaa si dareen leh oo fudud.

---

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)

Adeegsiga laba doorsoome ayaa tan si fudud ku xalinaya:

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

waxaadna heleysaa wax soo saarka aad rabto:

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