条目排名和分组依据

发布于 2024-12-11 17:37:00 字数 596 浏览 0 评论 0原文

我有一个网站，人们可以在其中保存游戏中的高分。我一直在尝试通过查找更高的其他高分数量来为玩家的高分分配排名。因此，如果查询找到 5 个人的高分较高，则该玩家的高分排名将为 6。

事实是，数据库中可以为每个用户记录多个分数（针对同一游戏）。这就是为什么当我只想显示玩家在游戏中的最佳得分（而不是他在该游戏中的所有得分）时使用GROUP BY user。

这是我现在使用的代码。它不起作用，因为查询似乎总是返回 2（就像它总是返回只有一个分数比玩家的最高分高一样）。如果我删除temp GROUP BY user，它会返回一个半正确的值，因为计算给定游戏中每个玩家的所有分数（如果一个玩家在游戏中作为多个分数）。

$count3 = mysql_result(mysql_query("SELECT COUNT(*) + 1 as Num FROM (SELECT * FROM ava_highscores WHERE game = $id AND leaderboard = $get_leaderboard[leaderboard_id] AND score > '$highscore2[score]') temp GROUP BY user");

原文

I have a website where people are saving highscores in games. I've been trying to assign a rank to a player's highscore by finding the number of other highscores that are higher. So if the query finds 5 people with higher highscores, the player's highscore rank will be 6.

The thing is that more than one score (for the same game) can be recorded in the database for every user. This is why I'm using GROUP BY user when I want to display only a player's best score in a game (not all of his scores in that game).

Here's the code I am using now. It doesn't work, since the query seems to always return 2 (like if it was always returning that there was only one score higher than the player's highscore). If I remove temp GROUP BY user, it returns an half-correct value, since counting all the scores (if a player as multiple scores in a game) from every player in a given game.

$count3 = mysql_result(mysql_query("SELECT COUNT(*) + 1 as Num FROM (SELECT * FROM ava_highscores WHERE game = $id AND leaderboard = $get_leaderboard[leaderboard_id] AND score > '$highscore2[score]') temp GROUP BY user");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伏妖词 2024-12-18 17:37:00

当您使用 GROUP BY 时，COUNT 返回每个组的行数，而不是结果集中所有行的单个计数。请改用 COUNT(DISTINCT ...)。而且你实际上并不需要内部选择。您可以将其全部编写为单个查询：

SELECT COUNT(DISTINCT `user`) + 1 AS Num
FROM ava_highscores
WHERE game = '3'
AND leaderboard = '5'
AND score > '1000'

注释

确保您的 score 列是数字类型（而不是 varchar 类型），以便比较正确进行。
在(game,leaderboard,score)上添加索引将使查询更加高效。索引中列的顺序也很重要。

When you use GROUP BY then COUNT returns a count of rows per group rather than a single count of all rows in the result set. Use COUNT(DISTINCT ...) instead. Also you don't actually need the inner select. You can write it all as a single query:

SELECT COUNT(DISTINCT `user`) + 1 AS Num
FROM ava_highscores
WHERE game = '3'
AND leaderboard = '5'
AND score > '1000'

Notes

Make sure that your score column is a numeric type (not a varchar type) so that the comparison works correctly.
Adding an index on (game, leaderboard, score) will make the query more efficient. The order of the columns in the index is also important.

回复收藏 0 原文

~没有更多了~