SQL Lesson 12: 查询执行顺序

 1.【复习】统计出每一个导演的电影数量（列出导演名字和数量）

SELECT Director, count(title) FROM movies group by Director;

2.【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)

SELECT Director,sum(Domestic_sales+International_sales) FROM movies join Boxoffice on id=movie_id group by Director;

3.【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量) ✓

SELECT Director,sum(Domestic_sales+International_sales) s ,count(title) c,avg(Domestic_sales+International_sales) a FROM movies join Boxoffice on id=movie_id 
group by Director having c>1 order by a  desc limit 1;

4.【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

select title,(select max(Domestic_sales+International_sales)from Boxoffice) -(Domestic_sales+International_sales) from  Movies join Boxoffice on id=movie_id;