获取mysql中特定用户的排名 [英] get rank of specific users in mysql

问题描述

我有一个包含所有用户的表，还有另一个包含该表子集的数组.
例如:表包含 1,2,3,4,5,6,7,8,9,10

I have a table of all users and I have another array which has subset of that table.
Ex:table contains 1,2,3,4,5,6,7,8,9,10

我的用户:2、4、6、8、10:

my users: 2,4,6,8,10:

我的用户之间的排名 w.r.t 与 1,3,5,7,9 无关

Ranking of my users among themselves w.r.t points and has nothing to do with 1,3,5,7,9

我目前有这张桌子:

create table uservotes(id int, name varchar(50), vote int);

INSERT INTO uservotes VALUES
(1, 'A', 34),
(2, 'B', 80),
(3, 'bA', 30),
(4, 'C', 8),
(5, 'D', 4),
(6, 'E', 14),
(7, 'F', 304),
(8, 'AA', 42),
(9, 'Ab', 6),
(10, 'Aa', 10);

如何在 2,4,6,8,10 中获得排名

How do I get ranking among 2,4,6,8,10

我正在寻找的答案:

id    rank    votes   name
2       1       80     B
4       5        8     C
6       3       14     E
8       2       42     AA
10      4       10     Aa

非常感谢您的帮助.提前致谢.

I really appreciate any help.Thanks in Advance.

推荐答案

我怀疑这就是你想要的:

I suspect that this is what you want:

select uv.*, (@rank := @rank + 1) as rank
from uservotes uv cross join
     (select @rank := 0) const
where uv.id in (2, 4, 6, 8, 10)
order by votes desc;

这是在 MySQL 中有效计算排名的标准方法，以及用于选择 ID 的 where 子句.

This is the standard way of calculating a rank efficiently in MySQL, along with a where clause to choose your ids.

在开始之前，在大多数数据库中，您只需使用 row_number() 或 dense_rank() 来达到此目的.MySQL 不支持此 ANSI 标准功能.

Before starting, in most databases you would simply use row_number() or dense_rank() for this purpose. MySQL does not support this ANSI standard functionality.

关键是变量@rank.子查询 const 将其初始化为 0.然后查询运行.where 子句只获取您感兴趣的行.order by 然后将它们按投票排序，最大的投票在前.最后，@rank := @rank + 1 as rank 都会更新 @rank 变量并将其分配给输出中的一列.

The key is the variable @rank. The subquery const initializes this to 0. Then the query run. The where clause gets only the rows you are interested in. The order by then puts them in order by votes, with the biggest votes first. Finally, the @rank := @rank + 1 as rank both updates the @rank variable and assigns it to a column in the output.

这篇关于获取mysql中特定用户的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！