I kēia lā ua ʻike wau i ka hana aʻe ma ka hana ʻana i kahi papahana mea kūʻai: E lawe i kahi pākaukau "SQL" me nā kolamu "id" a me "helu" a hoʻoholo i kahi papa inoa papa inoa o nā mea hoʻohana a pau i hoʻokaʻawale ʻia e ka "helu" i hoʻohana ʻia me nā e kiʻi i ka pae like me ka helu like. Hiki ke hoʻonā maʻalahi ʻia kēia hana ma o ka hoʻohana ʻana i nā loli i ho'ākāka ʻia e ka mea hoʻohana .
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)
Hoʻoholo maʻalahi ka hoʻohana ʻana i ʻelua mau loli i kēia
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
a loaʻa iā ʻoe ka hoʻopuka āu e makemake ai
+--------+-----------+----------+ | *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 | +--------+-----------+----------+