来自 www.db-class.com 的 SQL 查询

Question

我正在解决 www.db-class.com 练习。尽管已经很晚了，但问题仍然很有趣。我偶然发现了 extras 中的最后一个任务，但无法找到解决方案。

SQL方案如下:

create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text); 
create table Rating(rID int, mID int, stars int, ratingDate date);

可以得到整个数据库here

问题如下:

Q12 For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.

要提供更具体的详细信息，这里的问题是什么:

一次查询

select m.mid, m.title, max(r.stars) as stars 
from rating r natural join movie m 
where m.director is NOT NULL group by m.mid

返回评分​​最高的电影的 ID:

101 Gone with the Wind       4
103 The Sound of Music       3
104 E.T.                     3
107 Avatar                   5
108 Raiders of the Lost Ark  4

另一个查询

select m.director, max(r.stars) as stars 
from rating r natural join movie m 
where m.director is NOT NULL group by m.director 

返回电影评分最高的导演姓名:

James Cameron       5
Robert Wise         3
Steven Spielberg    4
Victor Fleming      4

如何加入查询并获取导演评分最高的电影的名称和明星:

James Cameron      Avatar                   5
Robert Wise        The Sound of Music       3
Steven Spielberg   Raiders of the Lost Ark  4
Victor Fleming     Gone with the Wind       4

Answer 1

在我看来，这只是一个 gretest-n-per-group 问题。甚至还有一个 tag为此 :)

与典型情况唯一不同的是，分组数据(director)在一张表中，而用来做比较的数据在另一张表(stars)中。因此，如果您查看这样标记的问题，您应该会找到类似的示例。

我建议您先解决它，假设您的所有数据都在一个表中(糟糕，但更容易解决……至少对我而言)。然后切换到拆分后的数据。

剧透:

我认为这是解决这个问题的方法，所以只有在您已经提出解决方案时才看一下。

select distinct m1.director, m1.title, r1.stars from movie m1
join rating r1 on m1.mID = r1.mID
left join (
    select m2.director, r2.stars from movie m2
    join rating r2 on m2.mID = r2.mID
) s on m1.director = s.director and r1.stars < s.stars
where s.stars is null and m1.director is not null