按分组最大列选择行

Question

我正在尝试为一个视频游戏创建高分榜。

有一个users表：

还有一个scores表：

我已经尝试使用以下查询：

SELECT MAX(`scores`.`score`) AS max_score,
    `scores`.`team`,
    `users`.`name`
FROM `scores`
    LEFT JOIN `users`
    ON `users`.`id` = `scores`.`user_id`
GROUP BY `scores`.`user_id`
ORDER BY max_score DESC
LIMIT 50

这将返回这个结果（示例）：


如您所见，用户“FUNTIMES”的得分“210”被归属于“老鹰”，但根据“scores”表格，它应该被归属于“维京人”（“FUNTIMES”的“user_id”为11）。

我该如何解决这个问题？像这样的做法非常完美：

SELECT `scores`.`score`, `scores`.`team`, `users`.`name`
FROM `scores`
LEFT JOIN `users`
ON `users`.`id` = `scores`.`user_id`
WHERE `scores`.`id` IN (
    SELECT MAX(`scores`.`score`)
    FROM `scores`
    GROUP BY `scores`.`user_id`
)
ORDER BY `scores`.`score` DESC
LIMIT 50

当然，我不能选择 WHERE id 是 IN score。我完全迷失了。

提前感谢任何帮助。

编辑：抱歉，我忘记提到：每个用户在高分表中只能显示一次。(这就是为什么我在查询中使用 GROUP BY user_id 的原因。)

- Leng

---

使用Oracle的MySQL无法以可扩展的方式使用单个查询来完成此操作。请尝试Percona或MariaDB。 - Aron

@Leng 我会谨慎选择措辞。这个方法可以处理小数据集...但是MySQL将使用嵌套内连接，这是一个O(n^2)的操作。 - Aron

@Aron：我明白了。抱歉，你是正确的。 - Leng

@Leng，你可以使用SPROC高效地完成它...但这不止是一个查询。 - Aron

@Aron 这太疯狂了 - MySQL怎么可能无法扩展这么简单的东西？我感到非常惊讶。 - Leng

Answer 1

SQL Fiddle

MySQL 5.5.32模式设置:

CREATE TABLE users 
    (`id` int, `name` varchar(8), `pass` varchar(4))
;

INSERT INTO users 
    (`id`, `name`, `pass`)
VALUES
    (2, 'JAKE', 'jake'),
    (3, NULL, NULL),
    (4, 'AWNO', 'ffff'),
    (5, 'FFF', 'ffff'),
    (6, 'ASDF', 'asdf'),
    (7, 'KKK', 'kkkk'),
    (8, 'AW YEAH', 'good'),
    (9, 'FFFF', 'ffff'),
    (10, 'FUNTIME', 'llll'),
    (11, 'FUNTIMES', 'llll'),
    (12, 'GOOD', 'good')
;

CREATE TABLE scores
    (`id` int, `user_id` int, `team` varchar(7), `score` int)
;

INSERT INTO scores
    (`id`, `user_id`, `team`, `score`)
VALUES
    (32, 9, 'vikings', 610),
    (33, 10, 'eagles', 290),
    (34, 11, 'eagles', 0),
    (35, 11, 'vikings', 40),
    (36, 11, 'vikings', 210),
    (37, 12, 'eagles', 170),
    (38, 12, 'eagles', 30)
;

查询1:

SELECT m.`max_score`,
    s.`team`,
    u.`name`
FROM `scores` s
    LEFT JOIN `users` u
    ON u.`id` = s.`user_id`
INNER JOIN (SELECT `user_id`, MAX(`score`) as max_score 
            FROM `scores` 
            GROUP BY `user_id`) m ON m.`user_id` = s.`user_id` AND
                                     m.`max_score` = s.`score`
ORDER BY max_score DESC
LIMIT 50

Results:

| MAX_SCORE |    TEAM |     NAME |
|-----------|---------|----------|
|       610 | vikings |     FFFF |
|       290 |  eagles |  FUNTIME |
|       210 | vikings | FUNTIMES |
|       170 |  eagles |     GOOD |

仅供讨论：

查询 1：

SELECT `user_id`, MAX(`score`) as max_score 
            FROM `scores` 
            GROUP BY `user_id`

结果:

| USER_ID | MAX_SCORE |
|---------|-----------|
|       9 |       610 |
|      10 |       290 |
|      11 |       210 |
|      12 |       170 |

- Fabien TheSolution

---

非常抱歉我没有为这个问题创建SQL fiddle，并且没有明确指出我只需要每个用户一次！您的查询现在确实可以工作。我已经给它点赞了，也很想将其标记为正确答案，但是在您编辑之前的瞬间，我刚刚将检查标记给了AgRizzo的（也是正确的）答案！非常抱歉，非常感谢您的时间和帮助。 - Leng

它有效...为每个用户ID提供最高分数。请查看我的回答底部... - Fabien TheSolution

这些索引 http://sqlfiddle.com/#!2/cad00a/2 将确保MySQL服务器不会创建一个需要通过磁盘IO读写进行排序的大型MyISAM磁盘临时表，尤其是在表非常大的情况下。 - Raymond Nijland

@RaymondNijland 在 SQLFIDDLE 中的 CREATE TABLE 仅用于演示查询。当然需要索引 :) - Fabien TheSolution

@Fabien TheSolution 我知道你的答案是正确的，所以我已经给它点赞了。我的评论只是额外提供的信息。 - Raymond Nijland

Answer 2

SELECT scores.score, scores.team, users.name
FROM users
JOIN scores
  ON users.id = scores.user_id
WHERE NOT EXISTS 
  (SELECT *
   FROM scores scores2
   WHERE scores2.score > scores.score
     AND scores2.user_id = users.id)

- AgRizzo

---

这将只返回一行（具有最高分数的单个用户）。 - Leng

忘记使用user.id - AgRizzo

如果您计划在几百个用户以上使用此查询，请先阅读 https://dev59.com/AlnUa4cB1Zd3GeqPaniQ#6778017。然后卸载Oracle MySQL并替换为MariaDB（开源MySQL）。 - Aron

@Aron - 你认为Fabien的查询是否存在相同的可扩展性问题？ - Leng

@Aron MySQL和MariaDB都是开源的。 - ypercubeᵀᴹ

Answer 3

类似这样的代码应该可以工作：

UPDATE result SET max_score = (SELECT SUM(score) FROM scores WHERE users.id=scores.user_id), team = (SELECT team FROM scores WHERE scores.user_id=users.id), name = (SELECT name FROM users WHERE users.id=scores.user_id) LIMIT 50;

- RugerSR9