如何获得排行榜? [英] 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和#2的排名值应为1,因为它们都具有30分
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屋!