在PHP/MySQL中计算等级 [英] Calculating rank in PHP/MySQL

问题描述

我在MySQL中有一个表，可以说例如它有两个字段Username，GameName和Score. 我想计算一个个人游戏名称的用户等级，以便查询

I've got a table in MySQL lets just say for example its got two fields Username, GameName and Score. I want to calculate the rank of a user for an indivudal game name so I could do the query

SELECT * FROM scores WHERE `GameName` = 'Snake' ORDER BY `Score` DESC

按从高到低的顺序获取所有用户的列表，并为每个用户分配一个编号.

to get a list of all users in order of highest to lowest and assign a number to each user.

但是有没有一种更简单的方法来获得个人用户的排名，而不是选择整个表，因为这似乎不太有效.

But is there an easier way to get the rank for an indivdual user rather than selecting the entire table as that doesn't seem too efficient.

谢谢

推荐答案

如果要获得整体排名，很遗憾，您必须对整个表格进行排序.简而言之，如果不知道表中的其他排名，就无法知道某人在表中的排名.

If you want overall rankings, you unfortunately have to sort the whole table. Simply put, you cannot know someone's rank in the table without knowing the other ranks in the table.

也就是说，如果您担心性能，这里有一个相当简单的解决方案-将排名查询的结果(可能存储到另一个MySQL表中)进行缓存，并为所有读取的内容进行查询.当有人发布新分数时，请重新计算您的临时表.您可以定期刷新某个排名以下的所有记录(例如，排名低于100的任何人都会从得分表中删除)，以保持重新计算的速度，因为没人会在被更高的得分击倒后排名上升.

That said, if you are worried about performance, there's a fairly easily solution here - cache the result of your ranking query (maybe into another a MySQL table!), and query that for all your reads. When someone posts a new score, recalculate your temporary table. You can periodically flush all records under a certain rank (say, anyone ranking under 100 gets removed from the scores table) to keep recomputations fast, since nobody would ever climb in rank after being knocked down by a higher score.

# 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;

然后，每当您想阅读游戏排名时:

Then, whenever you want to read a rank for a game:

select rank from leaderboards where game = '$game' and user_id = '$user_id';

这篇关于在PHP/MySQL中计算等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！