数据库的题(SQL Lesson 12: 查询执行顺序)

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

SELECT Director,count(*)from movies group by director

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

SELECT Director,sum( Domestic_sales+International_sales )
from
movies left join Boxoffice
where id=Movie_id
group by director

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

这个如有错误请指正

SELECT Director,
sum( Domestic_sales+International_sales ),
count()
    ,avg(Domestic_sales+International_sales) as va
from
movies left join Boxoffice
on id=movie_id
group by director having count(director)>1
order by va desc
limit 1

【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额 ✓
这道题解感谢@卿珏

select title,maxx-(Domestic_sales+International_sales) as cha from movies left join Boxoffice
on id = movie_id join(
select max(Domestic_sales+International_sales)as maxx from Boxoffice )