Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: [MySQL][PHP]Potrzebna rada mądrzejszego z MAX GROUP BY
Forum PHP.pl > Forum > Przedszkole
lucasnetwork
17.03.2013, 10:27:46
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 
  1. $i=1;
  2. $data1 = '2013-02-17 18:03:28';
  3. $data2 = '2013-03-17 18:03:28';
  4. $account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c 
  5. WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2' 
  6. GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed ASC LIMIT 5");
  7. while( $ranking = mysql_fetch_assoc( $account ))
  8. 	{
  9. 	$seconds = $ranking[secondsPlayed];
  10. 	$min = floor ($seconds / 60);
  11. 	$sec = $seconds % 60;
  12. 	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>';
  13. 	}
[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 
  1. $i=1;
  2. $data1 = '2013-02-17 18:03:28';
  3. $data2 = '2013-03-17 18:03:28';
  4. $account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c 
  5. WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2' 
  6. GROUP BY c.account_id ORDER BY wynik DESC LIMIT 5");
  7. 	while( $ranking = mysql_fetch_assoc( $account ))
  8. 	{
  9. 	$result = mysql_query("SELECT account_id, score, secondsPlayed FROM game WHERE score = ".$ranking[wynik]." AND account_id = ".$ranking[account_id]."");
  10. 	$result = mysql_fetch_assoc($result);
  11. 	$seconds = $result[secondsPlayed];
  12. 	$min = floor ($seconds / 60);
  13. 	$sec = $seconds % 60;
  14. 	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>';
  15. 	}
[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
17.03.2013, 10:41:07
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 
  1. 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
  2. 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
17.03.2013, 10:56:03
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 
  1. $i=1;
  2. $data1 = '2013-02-17 18:03:28';
  3. $data2 = '2013-03-17 18:03:28';
  4. $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 
  5. GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed LIMIT 5");
  6. 	while( $ranking = mysql_fetch_assoc( $account ))
  7. 	{
  8. 	$seconds = $ranking[secondsPlayed];
  9. 	$min = floor ($seconds / 60);
  10. 	$sec = $seconds % 60;
  11. 	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>';
  12. 	}
[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.
Michael2318
17.03.2013, 10:59:33
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 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) ?
lucasnetwork
17.03.2013, 11:12:29
W przypadku łączenia tablic account i game rezultat jest ten sam.

Testowany kod z pominięciem dle_users:
[PHP] pobierz, plaintext 
  1. $i=1;
  2. $data1 = '2013-02-17 18:03:28';
  3. $data2 = '2013-03-17 18:03:28';
  4. $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");
  5. 	while( $ranking = mysql_fetch_assoc( $account ))
  6. 	{
  7. 	$seconds = $ranking[secondsPlayed];
  8. 	$min = floor ($seconds / 60);
  9. 	$sec = $seconds % 60;
  10. 	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>';
  11. 	}
[PHP] pobierz, plaintext


Efekt ten sam, tylko w rankingu nie ma imion graczy, które były pobierane z dle_users.
Michael2318
17.03.2013, 11:14:20
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
17.03.2013, 11:18:16
Spoko, inaczej jest jak nie wie się, jak to wygląda smile.gif

Screeny:

account


dle_users


game


Porównywane wartości jak najbardziej są unikalne.
Michael2318
17.03.2013, 11:38:53
Cytat
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 
  1. 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
  2. GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed DESC LIMIT 5
[SQL] pobierz, plaintext
lucasnetwork
17.03.2013, 11:58:11
Na surowo wrzucone Twoje zapytanie do phpmyadmin.

Wynik:


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 smile.gif 53 to pierwszy rekord w tablicy game, gdzie account_id wynosi 6 smile.gif

Cytat(Michael2318 @ 17.03.2013, 11:38:53 ) *
No to przecież wystarczy użyć DESC zamiast ASC:

[SQL] pobierz, plaintext 
  1. 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
  2. GROUP BY c.account_id ORDER BY wynik DESC, c.secondsPlayed DESC LIMIT 5
[SQL] pobierz, plaintext


Nie pomaga. Wychodzi to samo.
Michael2318
17.03.2013, 12:05:04
Próbowałeś tak:
[SQL] pobierz, plaintext 
  1. 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
  2. GROUP BY c.account_id ORDER BY wynik DESC, maxtime DESC LIMIT 5
[SQL] pobierz, plaintext

?
lucasnetwork
17.03.2013, 19:17:20
Cytat(Michael2318 @ 17.03.2013, 12:05:04 ) *
Próbowałeś tak:
[SQL] pobierz, plaintext 
  1. 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
  2. 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 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 
  1. $i=1;
  2.     $data1 = '2013-02-17 18:03:28';
  3.     $data2 = '2013-03-17 18:03:28';
  4.     $account = mysql_query("SELECT a.*, b.*, c.*, MAX(c.score) AS wynik FROM account AS a, dle_users AS b, game AS c
  5.     WHERE a.auth=b.auth_user AND a.ac_id=c.account_id AND c.started BETWEEN '$data1' AND '$data2'
  6.     GROUP BY c.account_id ORDER BY wynik DESC LIMIT 5");
  7.     while( $ranking = mysql_fetch_assoc( $account ))
  8.     {
  9.     $result = mysql_query("SELECT account_id, score, secondsPlayed FROM game WHERE score = ".$ranking[wynik]." AND account_id = ".$ranking[account_id]."");
  10.     $result = mysql_fetch_assoc($result);
  11.     $seconds = $result[secondsPlayed];
  12.     $min = floor ($seconds / 60);
  13.     $sec = $seconds % 60;
  14.     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>';
  15.     }
[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 
  1. SELECT a.*, c.*, d.* FROM (SELECT DISTINCT account_id, MAX(score) AS wynik FROM game GROUP BY account_id) AS b 
  2. LEFT JOIN game AS a ON b.account_id = a.account_id AND b.wynik = a.score 
  3. LEFT JOIN account AS c ON a.account_id = c.ac_id 
  4. LEFT JOIN dle_users AS d ON c.auth = d.auth_user 
  5. WHERE d.auth_user IS NOT NULL AND a.started BETWEEN '$data1' AND '$data2' 
  6. ORDER BY b.wynik DESC, a.secondsPlayed ASC LIMIT 5
[SQL] pobierz, plaintext


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

Pozdrawiam
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2024 Invision Power Services, Inc.