MySQL do zamawiania wielokolumnowych ASC i DESC


Mam 2 tabele MYSQL,

użytkowników

i

wyceny
... Szczegół:
  • tabela użytkowników:

  • tabela wyników:

Moim zamiarem jest uzyskanie listy 20 użytkowników, którzy mają sortowanie pola

zwrotnica

DESC (malejąco) i sortuj


avg_time Pola 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 to:
Wynik jest nieprawidłowy, ponieważ pierwsza linia to dokładnie punkt = 100 i avg_time = 60.
Moje pożądane wyjście:
username point avg_time
demo123 100 60
demo123456 100 100
demo 90 120

Próbowałem wiele razy z różnymi zapytaniami, ale wynik był nadal zły. Czy mógłbyś podać mi jakieś rozwiązania?
Z góry dziękuję!
Zaproszony:
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Ok, myślę, że teraz rozumiem, czego chcesz, i pozwól mi wyjaśnić, aby potwierdzić, zanim zapytam. Potrzebujesz 1 wpisu dla każdego użytkownika. Chcesz, aby dla każdego użytkownika były rejestrowane jego najlepsze wyniki. Spośród najlepszych wyników na użytkownika chcesz uzyskać ten z najlepszym średnim czasem. Gdy masz już wszystkie „najlepsze” wartości wszystkich użytkowników, chcesz, aby ostateczne wyniki były najpierw sortowane według najlepszych punktów ... Prawie jak ocena konkurencji.
Więc teraz pytanie. Jeśli powyższe stwierdzenie jest poprawne, musisz zacząć od uzyskania najlepszego wyniku/średniego czasu na osobę i przypisania „Rangi” temu rekordowi. Można to łatwo zrobić ze zmiennymi MySQL @. Następnie po prostu włącz klauzulę HAVING, aby te wpisy zajmowały tylko 1 miejsce dla każdej osoby. Na koniec zastosuj kolejność 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 przetworzone przez SQLFiddle
http://sqlfiddle.com/#!2/d131d/6
Zauważ, że ze względu na wbudowane @variables wymagane do uzyskania odpowiedzi, na końcu każdego wiersza znajdują się dwie dodatkowe kolumny. To jest tylko „pozostałość” i można ją zignorować w dowolnej rzeczywistej prezentacji wyjściowej, którą próbujesz wykonać ... LUB możesz zawinąć całość o jeden poziom, aby uzyskać tylko kilka kolumn, które chcesz, na przykład
select 
PQ.UserName,
PQ.Point,
PQ.Avg_Time
from
( entire query above pasted here ) as PQ
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Myślę, że tęsknisz za stołem ..

użytkownicy: punkty = 1: *

samo
dołącz
nie jest rozwiązaniem.
czy to 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 w celu uzyskania średniego wyniku wszystkich użytkowników i punktu środkowego avg_time malejąco, rosnąco) avg_time
jeśli chcesz uzyskać każdy punkt oceny? używając
lewego sprzężenia zewnętrznego
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
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

@ DRapp to geniusz. Nigdy tak naprawdę nie rozumiałem, jak zakodował swój SQL, więc próbowałem zakodować go 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.
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

domyślna kolejność grupowa według identyfikatora pk ,, więc wynik

username point avg_time

demo123 100 90 ---> id = 4

wersja demonstracyjna 123456 100 100 --- & > id = 7

demo 90 120 --- & > id = 1

Aby odpowiedzieć na pytania, Zaloguj się lub Zarejestruj się