Query SQL kanggo nemtokake dhaptar peringkat08
14

Dina iki aku nemokake tugas ing ngisor iki nalika nggarap proyek pelanggan: Coba "panganggo" tabel SQL kanthi kolom "id" lan "skor" lan nemtokake rangking kabeh pangguna adhedhasar "skor" ing pangguna entuk rangking sing padha karo skor sing padha. Tugas iki bisa ditanggulangi kanthi intuisi lan gampang kanthi nggunakake variabel sing ditemtokake pangguna .

---

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)

Nggunakake rong variabel bisa ngatasi iki kanthi gampang

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

lan sampeyan entuk output sing dikarepake

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