Forum PHP.pl

Witam,

Posiadam prostą bazę danych szkoły, w której znajdują się tabele: students, teachers, courses i tsc (w której znajdują się informacje o tym jaki student miał jaki przedmiot, jak długo i z którym wykładowcą oraz ocena z tego przedmiotu). Na tej bazie próbuję np. zrealizować zapytanie wyboru roku studiów, na którym jest najwyższa średnia. Robię to w taki sposób:

[SQL] pobierz, plaintext 
SELECT TOP 1 STUDYEAR, AVG(GRADE) AS ILE FROM COURSES, TSC
    WHERE COURSES.CNO = TSC.CNO
    GROUP BY STUDYEAR ORDER BY 2 DESC;
[SQL] pobierz, plaintext 

Co działa, ale nie w przypadku, gdy kilka lat ma tę samą średnią i trzeba wyświetlić wszystkie. Zapytanie poniższe nie działa (pracuje pod SQL Serverem):

[SQL] pobierz, plaintext 
SELECT T.STUDYEAR, T.SREDNIA FROM (SELECT STUDYEAR, AVG(GRADE) SREDNIA FROM COURSES, TSC
    WHERE COURSES.CNO = TSC.CNO
    GROUP BY STUDYEAR) T
    WHERE T.SREDNIA = (SELECT MAX(SREDNIA) FROM T)
[SQL] pobierz, plaintext 

Istnieje jakiś prosty sposób realizowania tego typy zapytań, gdzie muszę użyć kilka razy funkcji agregujących razem z grupowaniem, bez korzystania z tabel tymczasowych? Ogólnie mam kilka tego typu zapytań (np. wybór najwyższej średniej, największej liczby przedmiotów ze wszystkich lat, przedmiotu, na który uczęszczało najmniej osób itp.). Chciałbym wiedzieć, jak najoptymalniej realizować tego typu zapytania.

Niestety musisz powielić podrzędne zapytanie wyliczające średnią także w podzapytaniu obliczającym MAX i liczyć, że optymalizator sobie to wykryje:

[SQL] pobierz, plaintext 
SELECT t.`studyear` FROM (SELECT `studyear`, AVG(`grade`) AS `ile` FROM `courses`, `tsc`
    WHERE `courses`.`id` = `tsc`.`id_course`
    GROUP BY `studyear` ORDER BY 2 DESC) t
WHERE t.`ile` = (SELECT MAX(p.`ile`) FROM (SELECT `studyear`, AVG(`grade`) AS `ile` FROM `courses`, `tsc`
    WHERE `courses`.`id` = `tsc`.`id_course`
    GROUP BY `studyear` ORDER BY 2 DESC) p)
[SQL] pobierz, plaintext 

Chodzi o to, że o ile podzapytania mogą być skorelowane z nadrzędnymi, to już muszą mieć całkowicie własne definicje tabelek. Ponieważ średnią wyliczasz w locie, musisz powielić całe liczące ją zapytanie. Jeśli napisałbyś samo

[SQL] pobierz, plaintext 
WHERE t.`ile` = (SELECT MAX(t.`ile`))
[SQL] pobierz, plaintext 

Wartość MAX byłaby policzona dla każdego wiersza osobno i ogólnie nie działałoby to tak, jak trzeba.

Aktualnie nie mam SQL Servera pod ręką, więc napisałem w innym dialekcie, ale myślę, że dasz radę sobie przerobić .