使用mysql获取当前排名 [英] Get current rank using mysql

问题描述

我有这个查询来获取行的排名，按某个字段排序

i've got this query to get the rank of rows, order by some field

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id, ammopacks
  FROM users
  GROUP BY id
  ORDER BY ammopacks DESC;

如何获得x"id的排名，而不是之前对它们进行排名?我不想知道所有用户的排名，只想知道一个 id.

How about getting the rank of "x" id, without ranking all them before? I don't want to know all users rank, just only one id.

有可能吗?

提前致谢

推荐答案

您可以使用子查询来做到这一点:

You can do this with a subquery:

select count(*) as rank
from users u
where u.ammopacks >= (select ammopacks from users u2 where u2.id = x)

这并不完全相同.这将进行真正的排名，其中具有相同 ammopacks 值的用户将具有相同的排名.在这种情况下，原始将给不同的用户不同的序列值.

This doesn't do exactly the same thing. This will do a real ranking, where users with the same value of ammopacks will have the same rank. The original would give different users different sequential values in this case.

要获得此效果，您可以:

To get this effect, you can do:

select count(*) as rank
from users u
where u.ammopacks > (select ammopacks from users u2 where u2.id = x) or
      (u.ammopacks = (select ammopacks from users u2 where u2.id = x) and
       u.id <= x
      )

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