mysql 根据分数分等级_使用mysql根据用户的最高得分计算其排名

I have a table (called users) I need rank of users based on their score but I want rank on the bases of users max score.

+-----------+------------+

| User_id | Score |

+-----------+------------+

| 1 | 12258 |

| 1 | 112 |

| 2 | 9678 |

| 5 | 9678 |

| 3 | 689206 |

| 3 | 1868 |

Expect result

+-----------+------------+---------+

| User_id | Score | Rank |

+-----------+------------+---------+

| 3 | 689206 | 1 |

| 1 | 12258 | 2 |

| 2 | 9678 | 3 |

| 5 | 9678 | 3 |

解决方案

You are looking for DENSE_RANK, But it supports mysql version higher than 8.0

use correlated-subquery to get max value by each User_id

use two variables one to store rank another to store previous value to make the DENSE_RANK number.

look like this.

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);

Query 1:

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 |