如何获取行排名？

Question

你好，

昨天我似乎发布了类似（或相同？）的问题，但我认为我需要发布一个新问题，因为我的问题简短明了。

我有以下表格：

id  point
1   30
2   30
3   29
4   27
5   28
6   26

我想要的：

1. 按等级获取所有用户。用户1和2应该有1作为他们的排名，因为他们都有30分

2. 我想通过用户ID查询排名。当我查询用户#1和#2时，我希望得到1作为我的排名结果，因为他们两个都有30分

添加于：3/18

我尝试了Logan的查询，但得到了以下结果

id point   rank
1   30  1
2   30  1
3   29  3
4   27  5
5   28  4
6   26  6

- Moon

---

可能是MySQL中的ROW_NUMBER()重复问题。 - Ciro Santilli OurBigBook.com

Answer 1

只需计算有多少人比他们得分更高。

select count(1) from users 
where point > (select point from users where id = 2) group by point

这将为您提供具有更多分数的用户数量。因此，对于用户1和用户2，结果将为0（零），表示他们是第一名。

- RDL

Answer 2

您看到的推荐子查询方法，其规模将呈二次增长。http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/展示了使用用户变量更高效的方法。以下是一个未经测试的适用于您问题的改编版本：

@points := -1; // Should be an impossible value.
@num := 0;

SELECT id
  , points
  , @num := if(@points = points, @num, @num + 1) as point_rank
  , @points := points as dummy
FROM `users`
ORDER BY points desc, id asc;

- btilly

---

我尝试在查询指定行时添加where语句，但是point_rank总是返回1。以下是我的查询： - Moon

SELECT *, vote_ratio , @num := if( @vote_ratio = vote_ratio, @num , @num +1 ) AS rank FROM challenge_photos where img_status = 1 and img_id = 65 ORDER BY vote_ratio DESC"; - Moon

@btilly //忘记提到你的ID了。 - Moon

@Moon：你需要做的是将我写的内容制作成一个视图，然后从视图中进行选择。如果你需要反复访问该视图，则将其保存到表中（如果需要可以是临时表），然后再进行访问。 - btilly

@btilly // 再次感谢！但问题是...这将是一个高流量的网站。我认为查询所有记录并在每次有人点击照片查看其排名时制作视图不是一个好主意。 - Moon

Answer 3

当我需要做类似的事情时，我创建了一个类似以下的视图：

CREATE VIEW rankings_view 
AS 
SELECT id
,      point
,      (select count(1) 
          from points b
         where  b.point > a.point) +1 as rank
FROM points as a;

假设原始表名为points。然后您可以通过查询视图来获取任何ID的排名或与任何排名对应的ID。

编辑

如果您想要计算每个点值上方不同点值的数量而不是具有高于当前点值的点值条目数，则可以执行以下操作：

CREATE VIEW rankings_view2
AS 
SELECT id
,      point
,      (SELECT COUNT(1) +1 AS rank 
          FROM ( SELECT DISTINCT point
                   FROM points b
                  WHERE   b.point >a.point ))
FROM points AS a;

注意

其他一些解决方案的性能肯定比这个更好。它们是针对mysql特定的，所以我不能真正用它们来做我正在做的事情。我的应用程序最多有128个实体需要排名，所以这对我来说已经足够好了。但是，如果您可能有大量行，您可能需要查看此处提供的其他解决方案之一或限制排名范围。

- lo5an

---

为什么创建这个视图？选择语句可以工作（尽管我会添加 ORDER BY rank ASC）。好的解决方案。 - Dawson

@Dawson 我喜欢将大部分的 SQL 放在数据库本身的视图和存储过程中。 - lo5an

@Logan // 在上方添加了查询结果 - Moon

@Moon 这不应该是个问题。你需要计算每个点值上方的不同点值数量，而不是行数。我会编辑我的帖子。 - lo5an

@Logan // 哇...感谢你的回答！现在它完美地工作了。 - Moon

Answer 4

如果先前有相同等级的重复点数，则OP希望跳过排名数字。例如，如下所示，由于排名1出现两次，因此2被跳过。

id  point  rank
1   30     1
2   30     1
3   29     3
4   27     4
5   28     5
6   26     6

这可以通过以下修改btilly的代码来实现：

set @points := -1; // Should be an impossible value.
set @num := 0;
set @c := 1;
SELECT id
  , points
  , @num := if(@points = points, @num, @num + @c) as point_rank
  , @c := if(@points = points, @c+1, 1) as dummy
  , @points := points as dummy2
FROM `users`
ORDER BY points desc, id asc;

- malhal

Answer 5

在MySQL 8中，您可以使用窗口函数，例如：

SELECT
  id,
  score,
  rank() over (order by amount) as ranking
FROM
  SomeTable

如果您需要选择仅一个行，请使用子查询：

SELECT
  id
  score,
  ranking
FROM (
  SELECT
    id,
    score,
    rank() over (order by score) as ranking
  FROM
    SomeTable
) t
WHERE
  id = ?

- golopot

Answer 6

SET @rank = 0, @prev_val = NULL;
SELECT id, @rank := IF(@prev_val=points,@rank,@rank+1) AS rank,
@prev_val := points AS points FROM users ORDER BY points DESC, id asc;

表：用户

- Dawson

---

前两个用户应该有相同的等级。 - btilly

是的，对此我感到抱歉。感谢您的批评指正。答案已经重新修改过了。 - Dawson

当修改你的回答时，你可以承认这与我在你修改之前发布的答案是相同的想法。 - btilly

抱歉，这真的是一个疏忽。我的解决方案已经测试并验证给OP了。我认为我更喜欢@Logan的方式。它更直接，更易读 - 更简单。 - Dawson

@Dawson：@Logan的解决方案确实更简单，但如果你的表中有10万行数据，性能会让你崩溃。 - btilly

@btilly - 的确。这是论坛的难点。你永远不知道基于许多帖子该如何回答。原始帖子可能只有一个100行的表格，或者这是一项关于语法/技术的家庭作业。 - Dawson