计算PHP / MySQL中的排名
我在MySQL中有一张表让我们只是说例如它有两个字段用户名，游戏名称和分数。 我想计算一个用户的等级为一个单独的游戏名称，所以我可以做这个查询
SELECT * FROM scores WHERE `GameName` = 'Snake' ORDER BY `Score` DESC
按照从高到低的顺序获得所有用户的列表，并为每个用户分配一个号码。
但是有没有一种更简单的方法来获得单个用户的排名，而不是选择整个表，因为这看起来效率不高。
谢谢
如果你想要整体排名，你不得不整理整个表。 简而言之，如果不知道表中的其他职位，就无法知道某人的排名。
也就是说，如果你担心性能，这里有一个相当简单的解决方案 - 缓存你的排名查询的结果（可能是另一个MySQL表！），并查询你的所有阅读。 当有人发布新分数时，请重新计算您的临时表。 您可以定期清除特定排名下的所有记录（比如，排名低于100的任何人都会从分数表中删除），以便快速实现重新计算，因为没有人会在被较高分数击倒后爬上排名。
# Create your overall leaderboards once
create table leaderboards (rank integer primary key, score_id integer, game varchar(65), user_id integer, index game_user_id_idx (game, user_id))
# To refresh your leaderboard, we'll query the ranks for the game into a temporary table, flush old records from scores, then copy
# the new ranked table into your leaderboards table.
# We'll use MySQL's CREATE TABLE...SELECT syntax to select our resultset into it directly upon creation.
create temporary table tmp_leaderboard (rank integer primary key auto_increment, score_id integer, game varchar(65), user_id integer)
select ID, GameName, UserID, from scores where GameName = '$game' order by score desc;
# Remove old rankings from the overall leaderboards, then copy the results of the temp table into it.
delete from leaderboards where game = '$game';
insert into leaderboards (rank, score_id, game, user_id)
select rank, score_id, game, user_id from tmp_leaderboard;
# And then clean up the lower scores from the Scores table
delete from scores join tmp_leaderboard on scores.id = tmp_leaderboard.score_id, scores.GameName = tmp_leaderboard.game where tmp_leaderboard.rank < 100;
# And we're done with our temp table
drop table tmp_leaderboard;
然后，每当你想读一个游戏的排名：
select rank from leaderboards where game = '$game' and user_id = '$user_id';
从用户表中获取用户标识并在查询中使用它
SELECT * FROM scores WHERE `GameName` = 'Snake'
and `youruseridfield` = '$useridvalue'
ORDER BY `Score` DESC
SELECT * FROM scores WHERE 'GameName' = 'Snake' && userID = '$userID' ORDER BY 'Score' DESC