MySQL，获取用户排名 [英] MySQL, Get users rank

问题描述

我有一个如下所示的mysql表:

I have a mysql table like below:

id     name     points
1      john     4635
3      tom      7364
4      bob      234
6      harry    9857

我基本上想获得单个用户等级，而不选择所有用户.我只想通过id选择一个用户，并获得用户排名，该排名取决于他们所获得的积分.

I basically want to get an individual user rank without selecting all of the users. I only want to select a single user by id and get the users rank which is determined by the number of points they have.

例如，返回ID为3的2级的Tom.

For example, get back tom with the rank 2 selecting by the id 3.

欢呼

Eef

推荐答案

SELECT  uo.*, 
        (
        SELECT  COUNT(*)
        FROM    users ui
        WHERE   (ui.points, ui.id) >= (uo.points, uo.id)
        ) AS rank
FROM    users uo
WHERE   id = @id

密集等级:

SELECT  uo.*, 
        (
        SELECT  COUNT(DISTINCT ui.points)
        FROM    users ui
        WHERE   ui.points >= uo.points
        ) AS rank
FROM    users uo
WHERE   id = @id

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