自学SQL网

SQL Lesson 12: 查询执行顺序

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

--请输入sql
SELECT director,
sum(Domestic_sales+International_sales) as total_sales,
count(*) as movies,
sum(Domestic_sales+International_sales)/count(*) as avg_sales
from movies join boxoffice
on movies.id=boxoffice.movie_id
group by director
having count(*)>1
order by avg_sales desc
limit 1

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

select title,
top.top_sales - (Domestic_sales+International_sales) as diff_sales
from movies 
left join boxoffice
on id = movie_id
cross join
(SELECT max(Domestic_sales+International_sales) as top_sales 
 from boxoffice) 
 as top

用到了一个cross join 交叉连接（笛卡尔积），单部电影的销冠作为一张单独的表。