我有三张桌子,分别是movie
、rating
和reviewer
movie
有4列movieID
、title
、year
、director
rating
有4列reviewerID
、movieID
、stars
、ratingDate
reviewer
有两列reviewerID
、name
我该如何查询reviewer
名对同一部电影的评分超过1次,并在第二次审查中给予更高评分的人.
这是我在查询中try 在两列中查找具有重复值的行(这意味着该电影已被一名 comments 者多次评分),然后不知何故,我需要查询reviewer
名在第二次评议中给出更高stars
分的人.
SELECT reviewer.name, movie.title, rating.stars, rating.ratingDate
FROM rating
INNER JOIN reviewer ON reviewer.rID = rating.rID
INNER JOIN movie ON movie.mID = rating.mID
WHERE rating.rID IN (SELECT rating.rID FROM rating GROUP BY rating.rID, rating.mID HAVING COUNT(*) > 1)
ORDER BY reviewer.name, rating.ratingDate;
movie
桌
movieID | Title | Year | Director |
---|---|---|---|
101 | Gone with the Wind | 1939 | Victor Fleming |
102 | Star Wars | 1977 | George Lucas |
103 | The Sound of Music | 1965 | Robert Wise |
104 | E.T. | 1982 | Steven Spielberg |
105 | Titanic | 1997 | James Cameron |
106 | Snow White | 1937 | null |
107 | Avatar | 2009 | James Cameron |
108 | Raiders of the Lost Ark | 1981 | Steven Spielberg |
rating
桌
reviewerID | movie ID | Stars | ratingDate |
---|---|---|---|
201 | 101 | 2 | 2011-01-22 |
201 | 101 | 4 | 2011-01-27 |
202 | 106 | 4 | null |
203 | 103 | 2 | 2011-01-20 |
203 | 108 | 4 | 2011-01-12 |
203 | 108 | 2 | 2011-01-30 |
204 | 101 | 3 | 2011-01-09 |
205 | 103 | 3 | 2011-01-27 |
205 | 104 | 2 | 2011-01-22 |
205 | 108 | 4 | null |
206 | 107 | 3 | 2011-01-15 |
206 | 106 | 5 | 2011-01-19 |
207 | 107 | 5 | 2011-01-20 |
208 | 104 | 3 | 2011-01-02 |
reviewer
桌
reviewerID | Name |
---|---|
201 | Sarah Martinez |
202 | Daniel Lewis |
203 | Brittany Harris |
204 | Mike Anderson |
205 | Chris Jackson |
206 | Elizabeth Thomas |
207 | James Cameron |
208 | Ashley White |
预期结果
Reviewer | Title |
---|---|
Sarah Martinez | Gone with the Wind |
编辑:我使用的是MySQL版本8.0.29