自学SQL网练习题

SQL Lesson 12: 查询执行顺序 

练习题4：

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

实现代码：

select title,
(select Domestic_sales+International_sales as sum_sale
FROM movies
left join Boxoffice on movies.id = Boxoffice.Movie_id
group by Title
order by sum_sale desc
limit 1) -Domestic_sales-International_sales 
as diff_sale

FROM movies
left join Boxoffice on movies.id = Boxoffice.Movie_id
group by Title
order by diff_sale desc

运行结果：

Title	Diff_sale
Toy Story	701213175
A Bug's Life	699773346
Cars	601188762
Toy Story 2	578156732
WALL-E	541860051
Brave	524188704
Cars 2	503319515
Monsters, Inc.	500355655
Ratatouille	439449093
The Incredibles	431729819
Up	331829167
Monsters University	319612304
Finding Nemo	126428650
Toy Story 3	0