[SQL学习 ]: 查询执行顺序

#XUESQL学习笔记#查询执行顺序
#[学习笔记的原文链接#

1、统计出每一个导演的电影数量（列出导演名字和数量）

SELECT director, count() FROM movies
group by director

2、统计一下每个导演的销售总额(列出导演名字和销售总额)

SELECT director, sum(Domestic_sales + International_sales) AS Totle_sales
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
group by director

#一大串字母实在是太烦人了#

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

SELECT director,sum(Domestic_sales + International_sales) AS sum_sales
,count(director),sum(Domestic_sales + International_sales)/count(director) AS avg_sales
FROM movies
LEFT JOIN boxoffice
ON movies.id = boxoffice.movie_id
group by director
having count(director) > 1
ORDER BY avg_sales DESC
LIMIT 1 

#这个难度比较大，很多都得用到#

4、找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

select title ,(select max(international_sales+domestic_sales) from boxoffice)-(international_sales+domestic_sales)  AS Margin
from movies 
left join boxoffice 
on movies.id=boxoffice.movie_id

#一大串字母，麻烦#