查找用户的最高分数和相关详细信息

Question

我有一个表，用户在其中存储分数和有关该分数的其他信息（例如分数注释或所用时间等）。我想要一个 mysql 查询来查找每个用户的个人最佳分数及其相关的注释和时间等。

我尝试使用的是这样的：

SELECT *, MAX(score) FROM table GROUP BY (user)

问题是虽然您可以从该查询 [MAX(score)] 中额外获得用户的个人最佳成绩，但返回的注释和时间等与最高分数无关，而是与不同的分数相关联（特别是 * 中包含的分数）。有没有办法编写一个查询来选择我想要的内容？或者我必须在 PhP 中手动完成？

Answer 1

我假设您只希望每个玩家获得一个结果，即使他们多次获得相同的最高分。我还假设您希望每个玩家第一次获得个人最佳成绩，以防出现重复。

有几种方法可以做到这一点。这是一种 MySQL 特定的方法：

SELECT user, scoredate, score, notes FROM (
    SELECT *, @prev <> user AS is_best, @prev := user
    FROM table1, (SELECT @prev := -1) AS vars
    ORDER BY user, score DESC, scoredate
) AS T1
WHERE is_best

这是一种使用普通 SQL 的更通用的方法：

SELECT T3.* FROM table1 AS T3
JOIN (
    SELECT T1.user, T1.score, MIN(scoredate) AS scoredate
    FROM table1 AS T1
    JOIN (SELECT user, MAX(score) AS score FROM table1 GROUP BY user) AS T2
    ON T1.user = T2.user AND T1.score = T2.score
    GROUP BY T1.user
) AS T4
ON T3.user = T4.user AND T3.score = T4.score AND T3.scoredate = T4.scoredate

结果：

1, '2010-01-01 17:00:00', 50, 'Much better'
2, '2010-01-01 14:00:00', 100, 'Perfect score'

测试数据 我用来测试这个：

CREATE TABLE table1 (user INT NOT NULL, scoredate DATETIME NOT NULL, score INT NOT NULL, notes NVARCHAR(100) NOT NULL);
INSERT INTO table1 (user, scoredate, score, notes) VALUES
(1, '2010-01-01 12:00:00', 10, 'First attempt'),
(1, '2010-01-01 17:00:00', 50, 'Much better'),
(1, '2010-01-01 22:00:00', 30, 'Time for bed'),
(2, '2010-01-01 14:00:00', 100, 'Perfect score'),
(2, '2010-01-01 16:00:00', 100, 'This is too easy');

Answer 2

您可以使用子查询进行连接，如下例所示：

SELECT   t.*,
         sub_t.max_score
FROM     table t
JOIN     (SELECT   MAX(score) as max_score,
                   user
          FROM     table
          GROUP BY user) sub_t ON (sub_t.user = t.user AND
                                   sub_t.max_score = t.score);

上述查询可以解释如下。它以：

SELECT t.* FROM table t;

...这本身显然会列出表的所有内容。目标是仅保留代表特定用户最高分数的行。因此，如果我们有以下数据：

+------------------------+
| user | score |  notes  | 
+------+-------+---------+
|    1 |    10 |  note a |
|    1 |    15 |  note b |    
|    1 |    20 |  note c |
|    2 |     8 |  note d |
|    2 |    12 |  note e |
|    2 |     5 |  note f |
+------+-------+---------+

...我们只想保留“note c”和“note e”行。

要找到我们想要保留的行，我们可以简单地使用：

SELECT MAX(score), user FROM table GROUP BY user;

请注意，我们无法从上面的查询中获取 Notes 属性，因为正如您已经注意到的，对于未使用 聚合函数，如 MAX() 或不是 GROUP BY 子句的一部分。要进一步阅读此主题，您可能需要检查：

• 揭穿 GROUP BY 神话
• MySQL如何决定在group by子句中返回哪个id？
• 为什么MySql允许不使用聚合函数的“group by”查询？< /a>

现在我们只需要保留第一个查询中与第二个查询匹配的行。我们可以使用INNER JOIN来做到这一点：

...
JOIN     (SELECT   MAX(score) as max_score,
                   user
          FROM     table
          GROUP BY user) sub_t ON (sub_t.user = t.user AND
                                   sub_t.max_score = t.score);

子查询的名称为sub_t。它是所有具有个人最好成绩的用户的集合。 JOIN 的 ON 子句将限制应用于相关字段。请记住，我们只想保留属于此子查询的行。

Answer 3

SELECT *
FROM table t
ORDER BY t.score DESC
GROUP BY t.user
LIMIT 1

旁注：指定字段比使用 SELECT * 更好