Disclaimer: Dieser Thread wurde aus dem alten Forum importiert. Daher werden eventuell nicht alle Formatierungen richtig angezeigt. Der ursprüngliche Thread beginnt im zweiten Post dieses Threads.
Wie könnte folgende SQL-Query gelöst werden?
Hallo zusammen,
wie würdet ihr diese Anfrage formulieren? (Entstammt dem Stanford Online-Kurs)
Movie ( mID, title, year, director )
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate )
“For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer’s name and the title of the movie.”
Viele Grüsse,
Speedy
Wenn du “twice” als “at least twice” liest, könnte es so gehen:
WITH double_ratings AS (
SELECT
r1.rID, r1.mID
FROM Rating r1
JOIN Rating r2 ON r2.rID = r1.rID AND r2.mID = r1.mID AND r2.ratingDate < r1.ratingDate AND r1.stars > r2.stars
GROUP BY r1.rID, r1.mID
) SELECT
Movie.title, Reviewer.name
FROM double_ratings
JOIN Movie ON Movie.mID = double_ratings.mID
JOIN Reviewer ON Reviewer.rID = double_ratings.rID
)
Insbesondere kann das Ergebnis Duplikate enthalten.
2 „Gefällt mir“
Alternativ könnte man erst mittels GROUP BY rID, mID und HAVING COUNT Bedingung alle eindeutig doppelten Reviews zusammensuchen, dann unter jenen weiter filtern.
Das “with” wird leider nicht erkannt.
Mein Vorschlag wäre das hier gewesen, allerdings habe ich dann statt dem Ergebnistupel zweimal “Sarah Martinez” “Gone With The Wind”, was ich einfach durch ein DISTINCT eliminiert habe:
SELECT DISTINCT re.name, mo.title
FROM Rating ra
Join Movie mo ON mo.mID=ra.mID
Join Reviewer re ON ra.rID=re.rID
WHERE ra.rID=(SELECT r1.rID
FROM Rating r1
JOIN Rating r2 ON r2.rID = r1.rID
AND r2.mID = r1.mID
AND r2.ratingDate < r1.ratingDate
AND r1.stars > r2.stars
GROUP BY r1.rID, r1.mID)
AND ra.mID=(SELECT r1.mID
FROM Rating r1
JOIN Rating r2 ON r2.rID = r1.rID
AND r2.mID = r1.mID
AND r2.ratingDate < r1.ratingDate
AND r1.stars > r2.stars
GROUP BY r1.rID, r1.mID);