您正在寻找DENSE_RANK
,但它支持高于8.0的MysqL版本
看起来像这样。
CREATE TABLE T(
User_id int,
score int
);
insert into t values (1,12258);
insert into t values (1,112);
insert into t values (2,9678);
insert into t values (5,9678);
insert into t values (3,689206);
insert into t values (3,1868);
:
SELECT User_id,score,Rank
FROM (
SELECT User_id,
score,
@rank :=IF(@prevIoUs = t1.score, @rank, @rank + 1) Rank,
@prevIoUs := t1.score
FROM T t1 CROSS JOIN (SELECT @Rank := 0,@prevIoUs := 0) r
WHERE t1.score =
(
SELECT MAX(score)
FROM T tt
WHERE t1.User_id = tt.User_id
)
ORDER BY score desc
) t1
:
| User_id | score | Rank |
|---------|--------|------|
| 3 | 689206 | 1 |
| 1 | 12258 | 2 |
| 2 | 9678 | 3 |
| 5 | 9678 | 3 |