MySQL select语句-如何计算当前排名 [英] MySQL select statement - How to calculate the current ranking

问题描述

我有一个名为user_rankings的表，其中存储了每个用户的投票(voted).我想显示用户当前的排名(本周)，该排名取决于用户获得的投票数.

I have a table called user_rankings where votes (voted) are stored for each user. I want to display the current ranking of users (this week) that depends on how much votes the user got.

要阐明的示例:

• RANK-NR，USERNAME，已投票
• 1，name1、18次
• 2，name1，16次
• (我在这里的排名)，我的名字，13次

• RANK-NR, USERNAME, VOTED,
• 1, name1, 18 times
• 2, name1, 16 times
• (my ranking here), myname, 13 times

在这个例子中，我的排名应该是3.如果我有17票，我将是2号.如果我上方有5个用户，我将是8号.我想你明白了.

In this example my ranking should be 3. If I'd have 17 votes, I would be number 2. If there would be five users above me, I would be number 8. I guess you get the point.

现在，我可以在PHP中以递增的$i轻松显示排名数字.但是我只想显示一个列表，该列表限制为十个用户(排名前十的列表)，如果我现在不在该排名前十的列表中，则直接显示该列表.所以我只是想知道如何使用MySQL获得确切的排名数字. 我假设此列表中有数百名用户，但票数不同.

Now I can display the ranking number easily with an incrementing $i in PHP. But I only want to show a list limited to ten users (a top ten list) and directly after that my current ranking, if I'm not already in that top ten list. So I'm just wondering how to get my exact ranking number using MySQL. I'm assuming to have hundreds of users in this list with a different amount of votes.

这是我目前的发言:

SELECT 
`voted`
FROM `users_ranking` 
WHERE 
`uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1

推荐答案

我刚刚发现自己该解决方案有效:

I just found out myself that this solution works:

SELECT *
FROM 
(
  SELECT  @ranking:= @ranking + 1 rank,
          a.`uid`
  FROM    `users_ranking` a, (SELECT @ranking := 0) b
  ORDER BY a.`votes` DESC
) s
WHERE `uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1

这篇关于MySQL select语句-如何计算当前排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！