MySQL ORDER BY несколько столбцов ASC и DESC

У меня есть 2 таблицы MYSQL, пользователи и оценки. Деталь:

таблица пользователей:

таблица оценок:

Мое намерение состоит в том, чтобы получить список 20 пользователей, у которых точка сортировка поля DESC (убывающая) комбинация avg_time поле сортировка ASC (по возрастанию). Я использую запрос:

SELECT users.username, scores.point, scores.avg_time
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20

Результат:

Результат неверен, потому что первая строка точно равна точке = 100 и avg_time = 60.

Мой желаемый результат:

username    point    avg_time
demo123      100        60
demo123456   100       100
demo         90        120

Я пробовал много раз с разными запросами, но результат по-прежнему ошибочен. Не могли бы вы дать мне несколько решений?

Спасибо заранее!

Ответ 1

Хорошо, я ДУМАЮ, Я понимаю, что вы хотите сейчас, и позвольте мне уточнить, чтобы подтвердить запрос. Вы хотите 1 запись для каждого пользователя. Для каждого пользователя вы хотите, чтобы их рейтинг BEST POINTS. Из лучших баллов на одного пользователя вы хотите получить лучшее среднее время. Когда у вас есть все "лучшие" значения для всех пользователей, вы хотите, чтобы конечные результаты отсортировались с лучшими моментами в первую очередь... Почти как ранжирование соревнования.

Итак, теперь запрос. Если приведенное выше утверждение является точным, вам нужно начать с получения наилучшего времени/среднего времени на человека и назначения "Ранга" для этой записи. Это легко сделать с использованием переменных MySQL @. Затем просто включите предложение HAVING, чтобы сохранить только те записи, которые были оценены 1 для каждого человека. Наконец, примените порядок по лучшим точкам и кратчайшему среднему времени.

select
      U.UserName,
      PreSortedPerUser.Point,
      PreSortedPerUser.Avg_Time,
      @UserRank := if( @lastUserID = PreSortedPerUser.User_ID, @UserRank +1, 1 ) FinalRank,
      @lastUserID := PreSortedPerUser.User_ID
   from
      ( select
              S.user_id,
              S.point,
              S.avg_time
           from
              Scores S
           order by
              S.user_id,
              S.point DESC,
              S.Avg_Time ) PreSortedPerUser
         JOIN Users U
            on PreSortedPerUser.user_ID = U.ID,
      ( select @lastUserID := 0,
               @UserRank := 0 ) sqlvars 
   having
      FinalRank = 1
   order by
      Point Desc,
      Avg_Time

Результаты, обработанные SQLFiddle

Обратите внимание, что из-за встроенных @variables, необходимых для получения ответа, в конце каждой строки есть два дополнительных столбца. Они просто "оставлены" и могут быть проигнорированы в любом фактическом представлении вывода, которое вы пытаетесь сделать... ИЛИ, вы можете обернуть всю вещь выше одного уровня, чтобы просто получить несколько столбцов, которые вы хотите, например,

select 
      PQ.UserName,
      PQ.Point,
      PQ.Avg_Time
   from
      ( entire query above pasted here ) as PQ

Ответ 2

Я думаю, что вы не понимаете о соотношении таблиц.

пользователи: score = 1: *

just join не является решением.

- это ваше намерение?

SELECT users.username, avg(scores.point), avg(scores.avg_time)
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY avg(scores.point) DESC, avg(scores.avg_time)
LIMIT 0, 20

(этот запрос позволяет получить среднюю точку пользователя и среднее значение avg_time по точке desc, asc) avg_time

если вы хотите получить рейтинг каждого рейтинга? используйте left outer join

SELECT users.username, scores.point, scores.avg_time
FROM scores left outer join users on scores.user_id = users.id
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20

Ответ 3

@DRapp - гений. Я никогда не понимал, как он закодировал свой SQL, поэтому я пробовал кодировать его в своем собственном понимании.


    SELECT 
      f.username,
      f.point,
      f.avg_time
    FROM
      (
      SELECT
        userscores.username,
        userscores.point,
        userscores.avg_time
      FROM
        (
        SELECT
          users.username,
          scores.point,
          scores.avg_time
        FROM
          scores
        JOIN users
        ON scores.user_id = users.id
        ORDER BY scores.point DESC
        ) userscores
      ORDER BY
        point DESC,
        avg_time
      ) f
    GROUP BY f.username
    ORDER BY point DESC

Он дает тот же результат, используя GROUP BY вместо пользователя @variables.

Ответ 4

по умолчанию порядок по pk id, поэтому результат имя пользователя avg_time
demo123 100 90 --- > id = 4
demo123456 100 100 --- > id = 7
демо 90 120 --- > id = 1