sql - MySQL:为用户获得最高分

Question

我有下表(高分)，

id      gameid      userid      name      score      date
1       38          2345        A         100        2009-07-23 16:45:01
2       39          2345        A         500        2009-07-20 16:45:01
3       31          2345        A         100        2009-07-20 16:45:01
4       38          2345        A         200        2009-10-20 16:45:01
5       38          2345        A         50         2009-07-20 16:45:01
6       32          2345        A         120        2009-07-20 16:45:01
7       32          2345        A         100        2009-07-20 16:45:01

现在在上面的结构中，一个用户可以多次玩游戏，但我想显示特定用户的“玩过的游戏”。所以在游戏部分我无法显示多个游戏。所以这个概念应该是，如果用户玩了 3 次游戏，那么应该显示得分最高的游戏。

我想要这样的结果数据:

id      gameid      userid      name      score      date
2       39          2345        A         500        2009-07-20 16:45:01
3       31          2345        A         100        2009-07-20 16:45:01
4       38          2345        A         200        2009-10-20 16:45:01
6       32          2345        A         120        2009-07-20 16:45:01

我尝试了以下查询，但没有给我正确的结果:

SELECT id, 
       gameid, 
       userid, 
       date, 
       MAX(score) AS score 
  FROM highscores
 WHERE userid='2345' 
GROUP BY gameid 

请告诉我这个查询是什么？

谢谢

Answer 1

要求有点模糊/令人困惑，但这样的东西能满足需求吗？
(特意添加了可能感兴趣的各种聚合)。

SELECT gameid, 
       MIN(date) AS FirstTime, 
       MAX(date) AS LastTime,
       MAX(score) AS TOPscore.
       COUNT(*)  AS NbOfTimesPlayed 
FROM highscores
WHERE userid='2345' 
GROUP BY gameid
-- ORDER BY COUNT(*) DESC -- for ex. to have games played most at top

编辑:关于将 id 列添加到 SELECT 列表的新问题
简短的回答是:“不，不能添加 id，不能添加到这个特定的构造中”。 (进一步阅读以了解原因)但是，如果目的是获得得分最高的游戏的 ID，则可以使用子查询修改查询以实现该目的。

正如 Alex M 在本页上所解释的，SELECT 列表中引用的所有列名以及未在聚合函数上下文中使用的列名(MAX、MIN、AVG、COUNT 等)，必须包含在ORDER BY 子句。 SQL 语言的这个规则的原因很简单，在收集结果列表的信息时，SQL 可能会遇到这样一个列的多个值(在 SELECT 中列出，而不是 GROUP BY)，然后不知道如何处理它； SQL 标准没有对这些额外的行/值做任何事情——可能有用但也可能很愚蠢——而是指示一条错误消息，以便用户可以修改查询并明确表达他/她的目标。

在我们的特定情况下，我们可以在 SELECT 中添加 id 并将其添加到 GROUP BY 列表中，但这样做时发生聚合的分组将不同:结果列表将包含尽可能多的行因为我们有 id + gameid 组合，所以每一行的聚合值将仅基于表中 id 和 gameid 具有相应值的记录(假设 id 是表中的 PK，我们会得到一个每个聚合行，使 MAX() 等变得毫无意义)。

包含与得分最高的游戏对应的 id(可能还有其他列)的方法是使用子查询。这个想法是子查询选择得分最高的游戏(在给定的组内)，并且主查询选择此行的任何列，即使字段不在(不可能)在子查询的组中- 通过构造。顺便说一句，请在此页面上感谢 rexem 首先显示此类查询。

SELECT H.id, 
       H.gameid, 
       H.userid, 
       H.name,
       H.score,
       H.date        
FROM highscores H
JOIN (
  SELECT M.gameid, hs.userid, MAX(hs.score) MaxScoreByGameUser
  FROM highscores H2
  GROUP BY H2.gameid, H2.userid
) AS M  
   ON M.gameid = H.gameid 
      AND M.userid = H.userid
      AND M.MaxScoreByGameUser = H.score
WHERE H.userid='2345' 

关于上述查询的一些重要说明

• 重复项:如果用户玩了多场达到相同高分的游戏，查询将生成那么多行。
• 子查询的 GROUP BY 可能需要根据查询的不同用途而改变。如果不是在每个用户的基础上搜索游戏的高分，我们想要绝对的高分，我们需要从 GROUP BY 中排除 userid(这就是为什么我用一个长而明确的名字命名 MAX 的别名)
• 为了提高效率，可以在子查询的 [now absent] WHERE 子句中添加 userid = '2345'(除非 MySQL 的优化器非常聪明，目前计算所有游戏+用户组合的所有高分，因此我们只需要为用户“2345”提供这些)；下侧重复；解决方案;变量。

有几种方法可以解决上述问题，但这些似乎超出了 [现在相当冗长] 解释 GROUP BY 结构的范围。