使用mysql根据用户的最高得分计算其排名 [英] Calculate rank of the users based on their max score using 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 |
期望结果
+-----------+------------+---------+
| User_id | Score | Rank |
+-----------+------------+---------+
| 3 | 689206 | 1 |
| 1 | 12258 | 2 |
| 2 | 9678 | 3 |
| 5 | 9678 | 3 |
推荐答案
您正在寻找DENSE_RANK
,但它支持高于8.0的mysql版本
You are looking for DENSE_RANK
, But it supports mysql version higher than 8.0
- 使用相关子查询按每个
User_id
来获取max
值 - 使用两个变量存储一个
rank
,另一个存储先前的值以生成DENSE_RANK
.
- use correlated-subquery to get
max
value by eachUser_id
- use two variables one to store
rank
another to store previous value to make theDENSE_RANK
number.
看起来像这样.
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);
查询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
结果 :
Results:
| User_id | Score | Rank |
|---------|--------|------|
| 3 | 689206 | 1 |
| 1 | 12258 | 2 |
| 2 | 9678 | 3 |
| 5 | 9678 | 3 |
这篇关于使用mysql根据用户的最高得分计算其排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文