找到每个电影的平均评分SQL [英] Finding the average rating of each movies SQL
问题描述
我目前正在数据库上的在线standford类，如果你可以帮助我解决这个sql问题，我会非常感谢。
I'm currently taking the online standford class on databases, If you could help me solve this sql problem I would greatly appreciate it. Sorry I'm a complete noob.
表电影：
mID | title | year | director
表格评分
rID | mID | stars | ratingDate
表审阅者
rID | name
对于所有审核人员对两个评论者给予同一电影评分，的评论者。消除重复，不要将评论者与自己配对，并将每个对包含一次。
For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don't pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order.
推荐答案
好的，对于每一对，按字母顺序返回对中的名称。你其实问了两个不同的问题。
Okay. You actually asked two different questions.
首先，我将回答您在帖子标题中提出的问题。
First, I'll answer the question you asked in the title of the post.
这应该可以工作。
select Movie.title, avg(Rating.stars) as AR from
Rating join Movie on Rating.mID = Movie.mID
group by Movie.title
order by AR desc
您不在联接中指定on子句，像这样
If you don't specify the "on" clause in the join, like this,
select Movie.title, avg(Rating.stars) as AR from
Rating join Movie
group by Movie.title
order by AR desc
那么所有电影将具有相同的评级，全局平均值，到期的连接的全交叉乘积。你想要一个名为内连接，在这种情况下，id。
then all movies will have the same rating, the global average, to due the full cross product of the join. You want an inner join on name or, in this case, id.
其次，这里是你问的另一个问题的答案，关于评论者对。 R1.name< R2.name 确保审阅者名称将从左到右排序。和 order by R1.name 确保这些对将从顶部到底部排序。
Second, here is the answer to the other question you asked, about pairs of reviewers. R1.name < R2.name ensures the reviewer names will be sorted left to right. and order by R1.name ensures those pairs will be sorted top to bottom.
select distinct R1.name, R2.name from
Rating Ra1 join Rating Ra2
join Reviewer R1 join Reviewer R2
where Ra1.mID = Ra2.mID and Ra1.rID != Ra2.rID
and R1.name < R2.name
and Ra1.rID = R1.rID and Ra2.rID = R2.rID
order by R1.name
这篇关于找到每个电影的平均评分SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
