Проблема с запросом MySQL для таблицы рекордов

У меня есть таблица рекордов MySQL для игры, которая показывает ежедневный рекорд для каждого из последних дней года. Прямо сейчас я выполняю PHP-цикл for и делаю отдельный запрос для каждого дня, но таблица становится слишком большой для этого, поэтому я хотел бы сжать ее в один простой оператор MySQL.

Вот мой новый запрос прямо сейчас (date_submitted — это метка времени):

SELECT date(date_submitted) as subDate, name, score FROM highScores WHERE date_submitted > "2009-07-16" GROUP BY subDate ORDER BY subDate DESC, score DESC LIMIT 10;

выход:

+------------+------------+--------+
| subDate    | name       | score  |
+------------+------------+--------+
| 2010-07-18 | krissy     | 959976 |
| 2010-07-10 | claire     | 260261 |
| 2010-07-05 | krissy     | 771416 |
| 2010-06-19 | krissy     | 698031 |
| 2010-06-18 | otli       | 264898 |
| 2010-06-15 | robbie     |  82303 |
| 2010-06-01 | dad        | 480469 |
| 2010-05-29 | vicente    | 124149 |
| 2010-05-27 | dad        | 564007 |
| 2010-05-26 | caleb      | 502623 |
+------------+------------+--------+

Моя проблема в том, что когда он сгруппирован по subDate, он получил наивысший балл для самой ранней метки времени этого дня, как вы можете видеть в следующем запросе:

SELECT name, score, date_submitted FROM highScores WHERE date(date_submitted)='2010-06-15' GROUP BY name ORDER BY score DESC;

выход:

+--------+--------+---------------------+
| name   | score  | date_submitted      |
+--------+--------+---------------------+
| john   | 304095 | 2010-06-15 22:58:02 |
| april  | 247126 | 2010-06-15 21:25:31 |
| orli   | 166021 | 2010-06-15 21:25:31 |
| robbie |  82303 | 2010-06-15 11:38:39 |
+--------+--------+---------------------+

Как видите, бедный Джон должен был быть лидером на 15.06.2010. Кто-нибудь может помочь? Надеюсь, это что-то очень простое, что я упускаю из виду. Я попытался использовать max(score) перед частью FROM в 1-м запросе, и это дало мне правильную оценку, но не перенесло имя.

Спасибо за любую помощь.


mysql sql aggregate-functions
person peterangler    schedule 24.07.2010    source источник

Ответы (2)


arrow_upward
0
arrow_downward 

SELECT userName, userScore, subDate FROM (
    SELECT 
        userName,
        userScrore,
        DATE(submitDate) as subDate,
        @rn := CASE WHEN @subDate = DATE(submitDate)
                THEN @rn + 1
                ELSE 1
           END AS rn,
        @subDate := DATE(submitDate)
    FROM (SELECT @subDate := NULL) vars, highScores
    ORDER BY submitDate, userScore DESC
) deriv
WHERE rn=1;

См. также ответ на другой вопрос о самой высокой записи для чего-либо

person Wrikken    schedule 24.07.2010
comment
Эй, спасибо. Это выходит за рамки моего опыта работы с MySQL, но я думаю, что начинаю это понимать. Однако, когда я запускаю это из командной строки MySQL: SELECT name, score, subDate FROM ( SELECT name, score, DATE(date_submitted) AS subDate, @rn ..... и т.д.... я получаю эту ошибку: ОШИБКА 1064 (42000): у вас есть ошибка в синтаксисе SQL; обратитесь к руководству, которое соответствует версии вашего сервера MySQL, на предмет правильного синтаксиса для использования рядом с «SELECT name, score, DATE(date_submitted) AS» в строке 2 - person peterangler; 24.07.2010
comment
Эй, спасибо. Я только что попробовал еще раз и переписал его вместо копипаста, и это сработало! Ошибка, которую я получал, была какой-то дополнительной кареткой с пробелами или чем-то еще из-за ее копирования. - person peterangler; 25.07.2010

arrow_upward
0
arrow_downward

Добавить

ORDER BY userScore DESC

в конце второго запроса.

person JNK    schedule 24.07.2010
comment
Я поставил ORDER BY score DESC в конце второго запроса. Так что этот запрос сортируется правильно. - person peterangler; 24.07.2010
comment
Я только что попытался изменить оценку DESC на оценку ASC в первом запросе, просто чтобы посмотреть, подтверждает ли он вторую часть ORDER BY, и он дал мне точно такой же вывод в первом запросе, так что это странно. - person peterangler; 24.07.2010
comment
похоже, что он исправил второй вывод, если я не ошибаюсь. - person JNK; 24.07.2010
comment
Да, теперь он сортирует второй вывод. Он показывает, что у Джона самый высокий балл за этот день, но все же в первом запросе не имеет значения, набираю ли я оценку ASC или оценку DESC. Я думаю, может быть, мне придется сделать что-то сложное, например, второй ответ. - person peterangler; 24.07.2010