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
Краткий ответ: "Нет, идентификатор не может быть добавлен, не в этой конкретной конструкции". (Читайте дальше, чтобы понять почему) Однако, если целью является получение идентификатора игры с наибольшим количеством очков, запрос может быть изменен с использованием подзапроса для достижения этого.

Как объяснил Алекс М на этой странице, все имена столбцов, на которые есть ссылки в списке SELECT и которые не используются в контексте агрегатной функции (MAX, MIN, AVG, COUNT и т. П.), ДОЛЖНЫ быть включены в ORDER BY пункт. Причиной этого правила языка SQL является просто то, что при сборе информации для списка результатов SQL может встретить несколько значений для такого столбца (перечисленных в SELECT, но не GROUP BY) и тогда не будет знать, как с ним работать; Вместо того, чтобы делать что-либо - возможно полезное, но возможно и глупое - с этими дополнительными строками / значениями, стандарт SQL диктует сообщение об ошибке, так что пользователь может изменить запрос и явно выразить свои цели.

В нашем конкретном случае мы могли бы добавить идентификатор в SELECT, а также добавить его в список GROUP BY, но при этом группировка, при которой происходит агрегация, будет другой: список результатов будет включать столько строк, сколько мы имеем В комбинациях id + gameid агрегированные значения для каждой строки будут основаны только на записях из таблицы, в которых id и gameid имеют соответствующие значения (при условии, что id - это PK в таблице, мы получим по одной строке на агрегацию делая MAX() и тому подобное совершенно бессмысленным).

Способ включения идентификатора (и, возможно, других столбцов), соответствующего игре с наибольшим количеством очков, осуществляется с помощью подзапроса. Идея состоит в том, что подзапрос выбирает игру с рейтингом TOP (внутри заданной группы по), а основной запрос SELECT выбирает любой столбец этой строки, даже если в группе подзапроса не было (не могло быть) по конструкции. Кстати, на этой странице нужно отдать должное 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 (поэтому я назвал псевдоним MAX длинным, явным именем)
• Userid = '2345' может быть добавлено в предложение [теперь отсутствует] WHERE подзапроса, в целях эффективности (если оптимизатор MySQL не очень умный, в настоящее время вычисляются все высокие оценки для всех комбинаций игра + пользователь, в результате чего мы они нужны только для пользователя '2345'); обратная сторона дублирования; решение; переменные.

Существует несколько способов решения упомянутых выше проблем, но они, по-видимому, выходят за рамки [сейчас довольно длинного] объяснения конструкций GROUP BY.

Answer 2

Каждое поле, которое имеется в вашем SELECT (когда присутствует предложение GROUP BY), должно быть либо одним из полей в предложении GROUP BY, либо же групповой функцией, такой как MAX, SUM, AVG и т. Д. В вашем коде userid технически это нарушает, но довольно безобидно (вы можете сделать свой код технически совместимым со стандартом SQL GROUP BY gameid, userid); поля id а также date в более серьезном нарушении - будет много идентификаторов и дат в течение одного GROUP BY set, и вы не говорите, как сделать одно значение из этого набора (MySQL выбирает более или менее случайные, более строгие механизмы SQL могут более полезно выдать ошибку).

я знаю, что вы хотите the идентификатор и дата, соответствующие максимальному количеству очков для данной группировки, но это не указано в вашем коде. Вам понадобится выборка или самостоятельное объединение, чтобы сделать это явным!

Answer 3

Использование:

SELECT t.id, 
       t.gameid, 
       t.userid, 
       t.name,
       t.score,
       t.date        
  FROM HIGHSCORES t
  JOIN (SELECT hs.gameid, 
               hs.userid,
               MAX(hs.score) 'max_score'
          FROM HIGHSCORES hs
      GROUP BY hs.gameid, hs.userid) mhs ON mhs.gameid = t.gameid
                                        AND mhs.userid = t.userid
                                        AND mhs.max_score = t.score
 WHERE t.userid = '2345'