MySQL Highscores-用户的个人排名:重复的条目导致不正确的值 [英] MySQL Highscores - User's Personal Ranks: Duplicate Entries Causing Incorrect Values

问题描述

我目前正在用PHP创建一个页面，该页面将在我网站的高分中显示用户及其排名.

I'm currently creating a page in PHP that will display a user and their ranks in my site's highscores.

但是，我有一个不同于其他字段的字段，并且没有按预期运行.

However, I have one field that is different from the rest and isn't functioning as intended.

这是我的桌子:

-- ----------------------------
--  Table structure for `playerstats`
-- ----------------------------
DROP TABLE IF EXISTS `playerstats`;
CREATE TABLE `playerstats` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `gamelevel` int(11) NOT NULL DEFAULT '0',
  `overall` int(11) NOT NULL DEFAULT '0',
  `overallxp` bigint(20) NOT NULL DEFAULT '0',
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

...以及一些示例数据:

...and some example data:

INSERT INTO `playerstats` VALUES ('14950', '123', '1495', '129825211')
INSERT INTO `playerstats` VALUES ('28967', '124', '1495', '127168799')
INSERT INTO `playerstats` VALUES ('95848', '121', '1495', '108481173')

这是我的查询:

SELECT count(*) + 1 FROM (SELECT uid, overall, overallxp, gamelevel FROM playerstats GROUP BY playerstats.uid) AS x WHERE overall > (SELECT overall FROM playerstats WHERE uid = ". $userid .")"

...并且$ userid为:

...and $userid is:

$userid = (int) $_GET['searched'];

现在，当我导航至userid14950的个人最高得分时，它会为该用户显示正确的总体排名，因为他们是整体而言xp最高的人.但是，当我访问userid28967或userid95848的个人最高分时，由于某些原因，它们的总体排名与userid14950相同(很可能是因为我没有考虑总体结果相同的用户).

Now, when I navigate to the personal highscores of userid14950, it displays the correct overall ranking for that user because they are the person with the highest overallxp for their overall. However, when I visit the personal highscores of userid28967 or userid95848, their overall rank is the same as userid14950 for some reason (most likely because I don't account for users with the same overall result).

我的问题是:如果两个(或更多)用户总体上共享相同的水平，他们将显示正确的排名，而不是重复的排名，那么我将如何做?

My question is: how would I go about making it so if two (or more) users share the same overall, they have their correct rank displayed, and not a duplicate one?

就是这样.
非常感谢您的任何帮助:)

So that's about it.
Any help is very much appreciated :)

谢谢，
标记

推荐答案

尝试一下:

SET @rank = 0;
SELECT rank FROM (
    SELECT @rank:=@rank + 1 AS rank, uid FROM playerstats ORDER BY overall DESC, 
    overallxp  DESC
) as tmp WHERE uid = 14950

avove查询将返回user14950的排名

the avove query will return the rank for user14950

此查询将列出所有用户及其等级

This query will list all the users and their ranks

SET @rank=0;
SELECT rank, uid, overall, overallxp FROM (
SELECT @rank:=@rank + 1 AS rank, uid, overall, overallxp FROM playerstats ORDER BY overall DESC, overallxp DESC
) as tmp 

这篇关于MySQL Highscores-用户的个人排名:重复的条目导致不正确的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！