[MySQL][PHP]Potrzebna rada mądrzejszego z MAX GROUP BY, Połączenie 3 tablic + GROUP BY + MAX SELECT Opcje

[lucasnetwork]

Witam Wszystkich,

potrzebuję rady kogoś mądrzejszego, bo od dwóch dni szukam po różnych forach rozwiązania, na blogach, w manualu i w żaden sposób nie mogę sobie poradzić. Mam 3 tablice przechowujące dane:

Tablica account: ac_id, auth
Tablica dle_users: user_id, name, auth_user
Tablica game: game_id, account_id, score, secondsPlayed, started

Chcę utworzyć ranking, który pokaże, który gracz zajmuje kolejno pierwsze, drugie, trzecie itd. miejsce sortując według ilość punktów (score), a w przypadku remisu - czasu gry (secondsPlayed). Kombinuję w różny sposób z podzapytaniami i tworząc pętle, ale nadal nie ma tego, co powinno być.

Poniżej ranking, który ma wyglądać następująco:
Miejsce | Gracz | Wynik | Czas
1 | Aneta | 24 | 2:07
2 | Marcin | 24 | 2:21
3 | Marek | 21 | 1:50

O ile nie ma remisu to problemu nie ma, jeśli jednak jest remis pojawia się problem z błędnym przypisywaniem czasu.

Kod zapytania MYSQL w PHP:

[PHP] pobierz, plaintext 
$i=1;
$data1 = '2013-02-17 18:03:28';
$data2 = '2013-03-17 18:03:28';
$account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c 
WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2' 
GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed ASC LIMIT 5");
while( $ranking = mysql_fetch_assoc( $account ))
	{
	$seconds = $ranking[secondsPlayed];
	$min = floor ($seconds / 60);
	$sec = $seconds % 60;
	echo '<tr align="center"><td>'.$i++.'</td><td>'.$ranking[name].'</td><td>'.$ranking[wynik].'</td><td>'.$min.':';if (strlen($sec)==1){echo '0';} echo $sec.'</td></tr>';
	}
[PHP] pobierz, plaintext 

Problem w tym, że prawidłowo jest pobierany najwyższy wynik gracza w określonym przedziale daty, ale pozostałe pola rekordu nie są prawidłowo przypisywane. Czas gry (secondsPlayed), który jest w rekordzie z najwyższym wynikiem (score) już nie jest pobierany z prawidłowego pola tylko pierwszy z całej tablicy, gdzie id gracza (account_id) znajduje się.

Aby to zobrazować poniżej kawałek tablicy game:

game_id | account_id | score | secondsPlayed
1 | 4 | 8 | 101
2 | 5 | 16 | 120
3 | 4 | 24 | 127

Zapytanie pobiera prawodłowo MAX(score) jako 24 grupując po ID gracza (w przykładzie account_id = 4), ale już czas gry (secondsPlayed) zamiast 127 sekund pobiera 101.

W jakimś stopniu znalazłem rozwiązanie w ten sposób:

[PHP] pobierz, plaintext 
$i=1;
$data1 = '2013-02-17 18:03:28';
$data2 = '2013-03-17 18:03:28';
$account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c 
WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2' 
GROUP BY c.account_id ORDER BY wynik DESC LIMIT 5");
	while( $ranking = mysql_fetch_assoc( $account ))
	{
	$result = mysql_query("SELECT account_id, score, secondsPlayed FROM game WHERE score = ".$ranking[wynik]." AND account_id = ".$ranking[account_id]."");
	$result = mysql_fetch_assoc($result);
	$seconds = $result[secondsPlayed];
	$min = floor ($seconds / 60);
	$sec = $seconds % 60;
	echo '<tr align="center"><td>'.$i++.'</td><td>'.$ranking[name].'</td><td>'.$ranking[wynik].'</td><td>'.$min.':';if (strlen($sec)==1){echo '0';} echo $sec.'</td></tr>';
	}
[PHP] pobierz, plaintext 

W pętli jest zapytanie, które wyszukuje znalezione wcześniej MAX(score) i porównuje je z ID gracza (account_id). W ten sposób przypisywany jest już właściwy czas secondsPlayed, ale jak to posortować, kiedy w punktach jest remis?

[Michael2318]

Zastanawia mnie czy dobrze łączysz tabele dle_users. W pozostałych dwóch masz account_id (acc_id), jednak w przypadku dle_users, nie powinieneś jej czasami łączyć po user_id ? Pytam bo nie wiem jakie wartości dokładnie trzymasz w tej kolumnie.

Spróbuj takiego zapytania:

[SQL] pobierz, plaintext 
SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM accounts a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth
GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed LIMIT 5
[SQL] pobierz, plaintext 

Na razie bez uwzględniania żadnego czasu, zobacz po prostu czy dobrze Ci to poukłada.
W powyższym zapytaniu połączyłem tabele dle_users z tabelą account, tak samo jak Ty to zrobiłeś, po kolumnach auth.

[lucasnetwork]

Dzięki za odpowiedź.

właśnie nie da się ich tak połączyć. Wspólnymi wartościami jest tutaj auth_user z tablicy dle_users i auth z tablicy game. Baza nie była tworzona przez mnie, ja tylko mam stworzyć ranking, ale nie sądziłem, że będzie z tym taki problem.

Całość kodu testowanego:

[PHP] pobierz, plaintext 
$i=1;
$data1 = '2013-02-17 18:03:28';
$data2 = '2013-03-17 18:03:28';
$account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth 
GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed LIMIT 5");
	while( $ranking = mysql_fetch_assoc( $account ))
	{
	$seconds = $ranking[secondsPlayed];
	$min = floor ($seconds / 60);
	$sec = $seconds % 60;
	echo '<tr align="center"><td>'.$i++.'</td><td>'.$ranking[name].'</td><td>'.$ranking[wynik].'</td><td>'.$min.':';if (strlen($sec)==1){echo '0';} echo $sec.'</td></tr>';
	}
[PHP] pobierz, plaintext 

Niestety rezultat ten sam. Pobiera najwyższy wynik prawidłowo, ale czas przypisuje już według pierwszego napotkanego rekordu z ID gracza.

Przed chwilą spróbowałem jeszcze łączenie tablic z pominięciem dle_users tylko game i account. Efekt ten sam, najwyższe score pobrane prawodłowo, ale secondsPlayed pierwsze napotkane.

Ten post edytował lucasnetwork 17.03.2013, 10:59:53

[Michael2318]

Musisz mieć wartość, dzięki której będziesz w stanie połączyć te tabele, inaczej to nie przejdzie (chyba, że dasz radę wyrzucić całkowicie tabelę dle_users i ranking zrobić bez niej).

EDIT:

pff, źle, sorry (IMG:style_emoticons/default/biggrin.gif) nie doczytałem, myślałem że napisałeś iż brak jakichkolwiek wspólnych wartości pomiędzy tymi tabelami, skoro jest jedna wartość wspólna to można to połączyć.
Możesz podać po kilka wpisów z każdej tabeli, żebym widział mniej więcej jak to wygląda (ew. screeny) ?

Ten post edytował Michael2318 17.03.2013, 11:13:07

[lucasnetwork]

W przypadku łączenia tablic account i game rezultat jest ten sam.

Testowany kod z pominięciem dle_users:

[PHP] pobierz, plaintext 
$i=1;
$data1 = '2013-02-17 18:03:28';
$data2 = '2013-03-17 18:03:28';
$account = mysql_query("SELECT a.*, b.*, MAX(b.score) AS wynik FROM account AS a, game AS b WHERE a.ac_id=b.account_id AND b.started BETWEEN '$data1' AND '$data2' GROUP BY b.account_id ORDER BY wynik DESC LIMIT 5");
	while( $ranking = mysql_fetch_assoc( $account ))
	{
	$seconds = $ranking[secondsPlayed];
	$min = floor ($seconds / 60);
	$sec = $seconds % 60;
	echo '<tr align="center"><td>'.$i++.'</td><td>'.$ranking[name].'</td><td>'.$ranking[wynik].'</td><td>'.$min.':';if (strlen($sec)==1){echo '0';} echo $sec.'</td></tr>';
	}
[PHP] pobierz, plaintext 

Efekt ten sam, tylko w rankingu nie ma imion graczy, które były pobierane z dle_users.

[Michael2318]

Pokaż kilka przykładowych wartości każdej z tabel (ew. screen).
Najlepiej jakby przykład dotyczył jednego i tego samego wpisu w każdej tabeli (czyli takie samo acc_id).

[lucasnetwork]

Spoko, inaczej jest jak nie wie się, jak to wygląda (IMG:style_emoticons/default/smile.gif)

Screeny:

account
(IMG:http://sklepolandia.pl/t_ac.jpg)

dle_users
(IMG:http://sklepolandia.pl/t_users.jpg)

game
(IMG:http://sklepolandia.pl/game.jpg)

Porównywane wartości jak najbardziej są unikalne.

[Michael2318]

game_id | account_id | score | secondsPlayed
1 | 4 | 8 | 101
2 | 5 | 16 | 120
3 | 4 | 24 | 127

Zapytanie pobiera prawodłowo MAX(score) jako 24 grupując po ID gracza (w przykładzie account_id = 4), ale już czas gry (secondsPlayed) zamiast 127 sekund pobiera 101.

No to przecież wystarczy użyć DESC zamiast ASC:

[SQL] pobierz, plaintext 
SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM accounts a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth
GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed DESC LIMIT 5
[SQL] pobierz, plaintext 

[lucasnetwork]

Na surowo wrzucone Twoje zapytanie do phpmyadmin.

Wynik:
(IMG:http://www.sklepolandia.pl/wynik.jpg)

Tablice, które wczesniej wrzuciłem w postaci screena sa większe toteż w wyniku dane mogą być z rekordów niewidocznych na screenach. Część można porównać, ale brak przypisywania widać już w ostatniej kolumnie wyniku zapytania. Kolumna "WYNIK" ma wartości 85, 75, 75, 19 i 13, a kolumna "SCORE" inne. Dlatego, że score pobrało już pierwsze napotkane rekordy z id gracza. Na tej samej zasadzie przekłamuje secondsPlayed.

Zobacz na przedostatni rekord w wyniku zapytania, bo można go porównać ze zrzutem tablicy game w poście wcześniej. Jest tam account_id 6 z wynikiem MAX(score) 19 (kolumna wynik). Jak widać w zrzucie tablicy game w rekordzie, gdzie account_id = 6 i MAX(score) = 19, secondsPlayed wynosi 7, a w wyniku zapytania jest 53 (IMG:style_emoticons/default/smile.gif) 53 to pierwszy rekord w tablicy game, gdzie account_id wynosi 6 (IMG:style_emoticons/default/smile.gif)

No to przecież wystarczy użyć DESC zamiast ASC:

[SQL] pobierz, plaintext 
SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM accounts a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth
GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed DESC LIMIT 5
[SQL] pobierz, plaintext 

Nie pomaga. Wychodzi to samo.

[Michael2318]

Próbowałeś tak:

[SQL] pobierz, plaintext 
SELECT a.*, b.*, c.*, MAX(c.score) AS wynik, MAX(c.secondsPlayed) AS maxtime FROM accounts a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth
GROUP BY c.account_id ORDER BY wynik DESC, maxtime DESC LIMIT 5
[SQL] pobierz, plaintext 

?

[lucasnetwork]

Próbowałeś tak:

[SQL] pobierz, plaintext 
SELECT a.*, b.*, c.*, MAX(c.score) AS wynik, MAX(c.secondsPlayed) AS maxtime FROM accounts a LEFT JOIN game c ON a.ac_id = c.account_id LEFT JOIN dle_users b ON b.auth_user = a.auth
GROUP BY c.account_id ORDER BY wynik DESC, maxtime DESC LIMIT 5
[SQL] pobierz, plaintext 

?

Tak, też próbowałem przypisać secondsPlayed do zmiennej. Nadal oszukuje, ale już inaczej (IMG:style_emoticons/default/smile.gif) Tworzy kolumnę z "maxtime" i wypisuje największy czas gry spośród wszystkich czasów danego gracza nadal pomijając ten prawidłowy.

Pewnym rozwiązaniem jest to, co pisałem w pierwszym poście, czyli:

[PHP] pobierz, plaintext 
$i=1;
    $data1 = '2013-02-17 18:03:28';
    $data2 = '2013-03-17 18:03:28';
    $account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c
    WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2'
    GROUP BY c.account_id ORDER BY wynik DESC LIMIT 5");
    while( $ranking = mysql_fetch_assoc( $account ))
    {
    $result = mysql_query("SELECT account_id, score, secondsPlayed FROM game WHERE score = ".$ranking[wynik]." AND account_id = ".$ranking[account_id]."");
    $result = mysql_fetch_assoc($result);
    $seconds = $result[secondsPlayed];
    $min = floor ($seconds / 60);
    $sec = $seconds % 60;
    echo '<tr align="center"><td>'.$i++.'</td><td>'.$ranking[name].'</td><td>'.$ranking[wynik].'</td><td>'.$min.':';if (strlen($sec)==1){echo '0';} echo $sec.'</td></tr>';
    }
[PHP] pobierz, plaintext 

W pętli zapytanie result identyfikuje już czas prawidłowo w oparciu o wcześniejsze zapytanie, gdzie pobrało MAX(score) i account_id, ale jak to posortować? W przypadku remisu pierwsze miejsce zajmuje gracz z czasem wyższym niż ten niżej, który punktów ma tyle samo, ale czas krótszy. Gdyby to było w jednym zapytaniu to MAX(score) DESC i secondsPlayed ASC - wtedy sortuje dobrze... hmm...

Gdyby ktoś kiedyś trafił na ten post szukając rozwiązania podobnego problemu, jak mój, podaję rozwiąznie, do którego doszedłem po 2 dniach kombinowania:

[SQL] pobierz, plaintext 
SELECT a.*, c.*, d.* FROM (SELECT DISTINCT account_id, MAX(score) AS wynik FROM game GROUP BY account_id) AS b 
LEFT JOIN game AS a ON b.account_id = a.account_id AND b.wynik = a.score 
LEFT JOIN account AS c ON a.account_id = c.ac_id 
LEFT JOIN dle_users AS d ON c.auth = d.auth_user 
WHERE d.auth_user IS NOT NULL AND a.started BETWEEN '$data1' AND '$data2' 
ORDER BY b.wynik DESC, a.secondsPlayed ASC LIMIT 5
[SQL] pobierz, plaintext 

Dziękuję Michael za wspólną "walkę".

Pozdrawiam