Sortowanie uczniów wg. ocen Opcje

[Userr]

Mam taką tabelę z polami: id INT, id_ucznia INT, imie_ucznia VARCHAR, oceny INT

Jej zawartość:
1, 1, Janek, 4
2, 3, Kasia, 5
3, 3, Kasia, 4
4, 2, Paweł, 3
5, 1, Janek, 4
6, 2, Paweł, 5
7, 2, Kasia, 5
8, 1, Janek, 2
9, 4, Robert, 3
10, 3, Kasia 4
11, 4, Robert, 4
12, 4, Robert, 4

Chciałbym, żeby wyświetlić uczniów w kolejności od najlepszych, do najsłabszych ocen. Z tym, że jeżeli jacyś uczniowie mają piątki, to liczy się liczba piątek (uczeń z większą liczbą piątek będzie przed uczniem z mniejszą liczbą piątek). Jeżeli okaże się, że jacyś uczniowie mają taką samą liczbą piątek, to liczy się liczba czwórek, itd. Chciałbym więc taką kolejność:

1. Kasia – 5, 5, 4, 4
2. Paweł – 5, 3
3. Robert – 4, 4, 3
4. Janek – 4, 4, 2

Jakim zapytaniem MySQL można by to było osiągnąć? Pewnie trzeba będzie wykorzystać GROUP BY i ORDER BY?

Ten post edytował Userr 27.11.2016, 13:14:34

[Userr]

Podejrzewam, że najwygodniej w CSS (lista numerowana lub licznik).
Jeśli w MySQL, to:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

P.S. Wydaje mi się, że w tym ostatnim rozwiązaniu (drugim z dwóch), też może być problem kiedy zdarzy się ktoś z dużą liczbą czwórek, taką, że: POW(10,5)*liczba_piatek<POW(10,4)*liczba_czworek (lub 4/3, 3/2, 2/1). W tym przypadku również podniesienie podstawy rozwiązuje w jakimś stopniu problem.

P.P.S. Twoja tabela ma niepoprawną strukturę. Powinna być rozbita na dwie: uczen, ocena. W tej chwili daną nadmiarową jest imię/dane osobowe ucznia w tabeli ocena.

Numerowanie zrobiłem z wykorzystaniem PHP - chodzi o to, że jak kilka osób będzie miało takie same oceny, to powinny mieć taką samą pozycję na liście, np:

1. Kasia 5,5,4,4
2. Pawel 5,3
3. Janek 4,4,3 [zmieniłem celowo 2 na 3, żeby Janek miał takie same oceny jak Robert]
3. Robert 4,4,3

Więc w PHP jak wyświetlałem w pętli foreach zawartość pobraną z bazy danych, to przy każdym obiegu pętli sprawdzałem, czy oceny z aktualnie pobranego wiersza są różne niż oceny z wiersza pobranego w poprzednim obiegu pętli. Jeżeli są różne, to zwiększałem licznik (bo to oznacza, że uczeń jest już niże na liście), jeżeli natomiast są takie same, to nie zwiększam bo uczniowie są na tym samym miejscu na liście (ponieważ mają identyczne oceny - np. Robert i Janek). I ten kod PHP jak widać działa dobrze, ale chciałbym z ciekawości zapytać w jaki sposób mógłbym połączyć ten kod:

[SQL] pobierz, plaintext 
SELECT @numer:=@numer+1 AS numer, imie, ocena FROM ocena, (SELECT @numer:=0) AS n
[SQL] pobierz, plaintext 

z poniższym zapytaniem?

[SQL] pobierz, plaintext 
SELECT o1.imie, GROUP_CONCAT(o1.ocena ORDER BY o1.ocena DESC)
FROM ocena AS o1
JOIN (SELECT imie, id_ucznia, MAX(ocena) AS maks FROM ocena GROUP BY id_ucznia) AS o2 ON o2.id_ucznia=o1.id_ucznia
GROUP BY o1.id_ucznia
ORDER BY POW(10,maks)*SUM(o1.ocena=o2.maks) DESC,SUM(o1.ocena) DESC
[SQL] pobierz, plaintext 

Próbowałem to połączyć, wstawić ten kod w różne miejsca, ale nie wychodzi. Pierwszy raz stykam się ze zmiennymi w MySQL i wydaje się to ciekawą rzeczą.

5,5,5 jest lepsze od 5,5, ale co ciekawsze 5,3,3 jest lepsze od samego 5 (IMG:style_emoticons/default/smile.gif)

Tak na prawdę mam tabelę z innymi danymi, ale na forum chciałem to przedstawić na przykładzie ocen szkolnych, żeby dobrze wyjaśnić o co mi chodzi, a okazało się, że trochę zagmatwałem, bo średnią ocen liczy się przecież zupełnie inaczej.
Co do nadmiarowości danych - w prawdziwej tabeli nie mam tego, ale tutaj dla przykładu robiłem tabelę trochę "na szybko" i stąd nadmiarowość.

Wiec w mojej prawdziwej tabeli będę miał dużo więcej danych tego typu, np:
5,5,5,4,4,4,4,3,3,3,3,3,3,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1

Ten post edytował Userr 28.11.2016, 12:19:39