选择分组到特定标识符的值 [英] Selecting values grouped to a specific identifer

问题描述

我有一个应用程序可以跟踪游戏中的高分。

I have an application that tracks high scores in a game.

我有一个 user_scores 表，用于将user_id映射到总谱。

I have a user_scores table that maps a user_id to a score.

我需要返回5个最高分，但对于任何特定用户只有1个高分。

I need to return the 5 highest scores, but only 1 high score for any specific user.

因此，如果用户X具有5个最高分

So if user X has the 5 highest scores on a purely numerical basis, I simply return the highest one and then the next 4 user scores.

我试图使用：

SELECT user_id, score 
FROM user_scores 
ORDER BY score DESC 
GROUP BY user_id 
LIMIT 5

但是似乎MySQL删除了多于一个分数的user_id。

But it seems that MySQL drops any user_id with more than 1 score.

推荐答案

这应该可以工作：

SELECT user_id, MAX(score)
FROM user_scores 
GROUP BY user_id 
ORDER BY MAX(score) DESC 
LIMIT 5

这篇关于选择分组到特定标识符的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！