Att lagra användardata som kolumnnamn är lite fult i min mening. Jag skulle lösa det så att man har tre tabeller: En för filmerna, en för användarna, och slutligen en med betygen. Betygen lagras då på med användarid, filmid samt betyg. Du måste med denna modell själv ansvara för att betygtabellen inte innehåller flera betyg från samma person, på samma film. (Alltså, uppdatera raden för en vissa kombination av film och användare om den redan existerar.)
Jag har gjort iordning ett litet testcase som du kan kika på. Bifogat som en fil är strukturen som jag använde samt lite testdata.
För att plocka ut datat använde jag följande:
SELECT
m.moviename AS moviename,
if(AVG(r.rating) IS NULL, 'Filmen har inte betygsatts ännu', AVG(r.rating)) AS rating,
COUNT(r.rating) AS num_reviewers,
GROUP_CONCAT(u.reviewername ORDER BY u.reviewername ASC SEPARATOR ', ') AS reviewers
FROM `test_movies` m
LEFT JOIN test_movierank r ON m.movieid = r.movieid
LEFT JOIN test_reviewers u ON u.reviewerid = r.reviewerid
WHERE 1
GROUP BY m.movieid
Det stora tricket är GROUP BY som plockat ut en film per rad och ändå låter dig använda aggregerande funktioner såsom AVG på varje rad som motsvarar en film.
JOIN'arna plockar ut rankning och användarnamn. Att det är en LEFT JOIN tillåter att högersidan av uttrycket så att säga kan returnera en tom mängd. (Om det inte finns några betyg för en viss film) Hade man använd INNER JOIN istället hade alla filmer utan betyg ignorerats.
AVG, COUNT och GROUP_CONCAT är alla aggregerande funktioner som utvinner saker från det hämtade datat. AVG returnerar uppebarligen medelbetyget, COUNT antalet betyg och GROUP_CONCAT slår ihop namnen på de som har betygsatt till en kommaseparerad sträng.
if(AVG(r.rating) IS NULL, 'Filmen har inte betygsatts ännu', AVG(r.rating)) AS rating är lite socker för att få skapa en anvöndarvänlig sträng. Du kan naturligtvis skriva AVG(r.rating) AS rating och då får du ut NULL-värden för filmer utan betyg.
Testfrågan har inga contraints, men du kan lägga till WHERE och ORDER BY efter behov.
Edit: LarsG hann före eftersom jag spenderade för mycket tid på att ordbajsa.
