mysql 排名 php_计算PHP / MySQL中的排名

如果你想要整体排名,不幸的是你必须对整个表进行排序.简单地说,如果不知道表中的其他排名,就无法知道某人在表中的排名.

也就是说,如果你担心性能,这里有一个相当容易的解决方案 – 缓存你的排名查询的结果(可能是另一个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';