给定用户名时，从无序的MySql数据库中获取基于分数的排名 [英] Get a rank, based on score, from an unordered MySql Database when given a Username

问题描述

好的，我有一个包含以下内容的表

Okay so I have a table that has the following

KEY   username   password   score  

以上各列没有特定顺序.

The above columns are not in any specific order.

我想给我的数据库一个用户名，并让我发回该用户名基于其分数的等级.因此，例如，如果我在那里有10个人，而其中的第三个人得分最高.当我传递第三人称用户名时，我希望它发送回1.

I want to send my Database a username and have it send me back what rank that user name is based on its score. So for example if I had 10 people in there and the 3rd person in has the highest score. When I pass the 3rd persons username in I want it to send back 1.

这可能吗?

我一直在尝试这样的事情

I have been trying things like this

$result = mysql_query("SELECT * FROM tablename where username='$username' ORDER BY score DESC");

但是它似乎没有给我行号

but it doesnt seem to give me the row number

推荐答案

这将处理得分相同的排名.

This will handle ranks that have the same score.

SELECT  d.*, c.ranks
FROM
        (
          SELECT    Score, @rank:=@rank+1 Ranks
          FROM
                  (
                      SELECT  DISTINCT Score 
                      FROM    tableName a
                      ORDER   BY score DESC
                  ) t, (SELECT @rank:= 0) r
        ) c 
        INNER JOIN tableName d
            ON c.score = d.score
// WHERE   d.username = 'Helen'

• SQLFiddle演示(具有重复项)
• SQLFiddle演示(带有过滤功能的 )
• SQLFiddle Demo (Ranking with duplicates)
• SQLFiddle Demo (with filtering)

例如

KEY     username    password    score   Ranks
1       Anna        123         5       3
2       Bobby       345         6       2
3       Helen       678         6       2
4       Jon         567         2       4
5       Arthur      ddd         8       1

为了获得更好的性能，请在列Score上添加INDEX

for better performance, add an INDEX on column Score,

ALTER TABLE tableName ADD INDEX (Score)

这篇关于给定用户名时，从无序的MySql数据库中获取基于分数的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！