mysql选择内部连接的*唯一值

I have 2 tables that look like this:

我有2个表看起来像这样：

users (uid, name)
-------------------
|  1  |  User 1   |
|  2  |  User 2   |
|  3  |  User 3   |
|  4  |  User 4   |
|  5  |  User 5   |
-------------------

highscores (user_id, time)
-------------------
|  3 |   12005    |
|  3 |   29505    |
|  3 |   17505    |
|  5 |   19505    |
-------------------

I want to query only for users that have a highscore and only the top highscore of each user. The result should look like:

我想仅查询具有高分的用户，并且仅查询每个用户的最高分。结果应如下所示：

------------------------
|  User 3 |   29505    |
|  User 5 |   19505    |
------------------------

My query looks like this:

我的查询如下所示：

SELECT user.name, highscores.time
FROM user
INNER JOIN highscores ON user.uid = highscores.user_id
ORDER BY time ASC 
LIMIT 0 , 10

Actually this returns multiple highscores of the same user. I also tried to group them but it did not work since it did not return the best result but a random one (eg: for user id 3 it returned 17505 instead of 29505).

实际上，这会返回同一用户的多个高分。我也尝试对它们进行分组，但它没有工作，因为它没有返回最好的结果而是随机的一个（例如：对于用户ID 3，它返回17505而不是29505）。

Many thanks!

非常感谢！

2 个解决方案

#1

You should use the aggregated function MAX() together with group by clause.

您应该使用聚合函数MAX（）和group by子句。

SELECT  a.name, MAX(b.`time`) maxTime
FROM    users a
        INNER JOIN highscores b
            on a.uid = b.user_id
GROUP BY a.name

SQLFiddle Demo

#2

Your effort of grouping users was correct. You just needed to use MAX(time) aggregate function instead of selecting only time.

您对用户进行分组的努力是正确的。您只需要使用MAX（时间）聚合函数而不是仅选择时间。

I think you wrote older query was like this:

我认为你写的旧查询是这样的：

SELECT name, time 
FROM users 
INNER JOIN highscores ON users.uid = highscores.user_id 
GROUP BY name,time

But actual query should be:

但实际查询应该是：

SELECT user.name, MAX(`time`) AS topScore
FROM users 
INNER JOIN highscores ON users.uid = highscores.user_id
GROUP BY user.name