如何获得排行榜? [英] How to get a row rank?

问题描述

HI

我昨天实际上张贴了类似(或相同?)的问题，但是我认为我需要发布一个新问题，因为我有一个简短但明确的问题.

I actually posted similar (or same?) question yesterday, but I thought I need to post a new question since I have short, but clear question.

我有下表.

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

我想要什么:

1. 按等级获得所有用户的订单.用户#1和#2的排名值应为1，因为它们都具有30分

1. get all the users order by rank. user #1 and #2 should have 1 as their rank value because they both have 30 points

我想按用户ID查询排名.当我查询用户#1和#2时，我希望获得1作为排名的结果，因为它们都得到30分

I want to query a rank by user id. I like to get 1 as the result of my rank when I query user #1 and #2 because both of them have 30 points

添加于:3/18

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

I tried Logan's query, but got the following result

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

推荐答案

您看到的推荐子查询方法将按平方缩放. http://www.xaprb. com/blog/2006/12/02/how-to-number-rows-in-mysql/显示了一种使用用户变量的更为有效的方法.这是针对您的问题的未经测试的适应方法:

The subquery approach that you have seen recommended will scale quadratically. http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ shows a much more efficient approach with user variables. Here is an untested adaptation to your problem:

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

这篇关于如何获得排行榜?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！