mysql - 斯坦福大学公开课的SQL多场比赛联接

你已经建立了一个新的电影分级网站，并且你已经收集了各种电影的评论员评分数据。数据还不多，但您仍然可以尝试一些有趣的查询。架构如下：
电影（中期、片名、年度、导演）
中文：有一部电影的身份证号码是mID，片名是，上映年份是，导演是。
审阅者（rID，姓名）
中文：ID号为rID的审阅者有一个特定的名字。
评级（rID、mID、stars、评级日期）
中文：影评人里德在某一天给了这部电影许多明星的评分（1-5）。
问题：
对于每个导演，返回导演的姓名以及他们导演的电影的片名（在所有电影中获得最高评价），以及该评价的价值。忽略导演为空的电影。
注意：查询是使用SQLite执行的，因此必须符合SQLite支持的SQL构造。
我的理解是：
每个导演可能有多部电影，每部电影由不同的评论员评分。这里的最高评级确实是最高的平均评级。我认为这个SQL应该包含多个子查询或连接，系统在我尝试了5次之后接受了它，并且花费了超过10分钟的时间。如果这是谷歌或Facebook的面试问题，我会失败的。有谁能给出一个简洁明了的解决方案？
我确实在网上搜索了这个解决方案，发现了很多错误的解决方案，尽管它们可以通过系统的小数据检查。例如，下面的SQL确实不正确。

select title, max(stars) as max_star, director
from Movie, Rating
where Movie.mID = Rating.mID and director is not NULL
group by director;

我的解决方案如下，如果有人能提供更优雅但逻辑正确的解决方案，请告诉我。谢谢

select Q2.director, title, maxStar from Movie m join
(select director, mID, max(stars) as maxStar from
(select director, mID, stars
from Movie m join Rating r using(mID)
where director NOT NULL
group by mID ) Q1
group by director ) Q2
on m.mID = Q2.mID;

最佳答案

请尝试以下操作：

Select Movie.title,
       MAX(A.rate),
       Movie.director (Select  rID, mID, AVG(stars) as rate, ratingDate
                       From Rating Group By mID
                       ) A
       Inner Join Movie on A.mID=Movie.mID
where Movie.director is not NULL
group by Movie.director;

关于mysql - 斯坦福大学公开课的SQL多场比赛联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26190672/