MySQL Zamów przez wiele kolumn ASC i DESC

Mam 2 tabele MYSQL, users i scores . Szczegóły:

  • tabela użytkowników:

Tutaj wpisz opis obrazka

  • tabela wyników:

Tutaj wpisz opis obrazka

Moim zamiarem jest uzyskanie listy 20 użytkowników, którzy mają point field sort DESC (malejąco) combine avg_time field sort ASC (rosnąco). Używam zapytania: 

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

Wynik jest następujący:

Tutaj wpisz opis obrazka

Wynik jest zły, ponieważ pierwsza linia jest dokładnie punkt = 100 i avg_time = 60.

Mój pożądany rezultat to: 

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

Próbowałem wiele razy z różnych zapytań, ale wynik jest nadal zły. Możesz mi podać jakieś rozwiązania?

Z góry dzięki!
Author: Tan Viet, 2012-12-04
Source

4 answers

Ok, myślę, że rozumiem, czego chcesz teraz, i pozwól mi wyjaśnić, aby potwierdzić przed zapytaniem. Chcesz 1 rekord dla każdego użytkownika. Dla każdego użytkownika, chcesz ich najlepszy wynik punktowy rekord. Z najlepszych punktów na użytkownika, chcesz ten z najlepszym średnim czasem. Po uzyskaniu przez wszystkich użytkowników "najlepszych" wartości, chcesz najpierw sortować wyniki końcowe z najlepszymi punktami... Prawie jak ranking zawodów.

Więc teraz Zapytanie. Jeśli powyższe stwierdzenie jest dokładne, musisz zacząć od uzyskanie najlepszego punktu / średniego czasu na osobę i przypisanie "rangi" temu wpisowi. Można to łatwo zrobić za pomocą zmiennych MySQL @. Następnie wystarczy dołączyć klauzulę o posiadaniu tylko tych rekordów w rankingu 1 dla każdej osoby. Na koniec zastosuj kolejność według najlepszych punktów i najkrótszego średniego czasu.

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

Wyniki obsługiwane przez SQLFiddle

Uwaga, Ze względu na zmienne inline @potrzebne do uzyskania odpowiedzi, na końcu każdego wiersza znajdują się dwie dodatkowe kolumny. Są to po prostu "left-over" i można je zignorować w dowolnej rzeczywistej prezentacji wyjściowej, którą próbujesz zrobić... Możesz też zawinąć całość powyżej jednego poziomu, aby uzyskać tylko kilka kolumn, które chcesz, jak 

select 
      PQ.UserName,
      PQ.Point,
      PQ.Avg_Time
   from
      ( entire query above pasted here ) as PQ
 23
Author: DRapp,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-04 11:39:11

Myślę, że nie rozumiesz o relacji tabeli..

Users: scores = 1: *

Po Prostu join nie jest rozwiązaniem.

Czy to jest Twój zamiar? 
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

(to zapytanie, aby uzyskać średni punkt dla każdego użytkownika i średni avg_time przez punkt desc, asc) avg_time

Jeśli chcesz uzyskać każdy ranking wyników? użyj 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
 1
Author: namxee,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-04 02:33:22

@ DRapp to geniusz. Nigdy nie rozumiałem, jak kodował swój SQL, więc próbowałem go kodować w moim własnym rozumieniu.


    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

Daje ten sam wynik, używając GROUP BY zamiast user @ variables.

 1
Author: Rad Apdal,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-07-02 15:19:41

Grupuj według domyślnej kolejności według pk id,więc wynik
username point avg_time
demo123 100 90 ---> id = 4
demo123456 100 100 ---> id = 7
demo 90 120 ---> id = 1

 0
Author: babaoqi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-04 08:57:28