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