小挑战←→0 / 70

卖了几个座位

#068小挑战连接NULL约 7 分钟

电影院要一张表：每部电影一共卖了几个座位（seats 加起来）。模板已经用 LEFT JOIN 把没卖票的电影也留下了，但它们的总数是空的；改成 0。要 title 和座位数两列。

用到的表

movies

id	title	genre	minutes	rating
1	Robot Summer	family	95	8
2	Dark Lake	horror	110	6
3	Space Pals	family	88	7
4	The Long Road	drama	142	9
5	Laugh Track	comedy	101	5
6	Snow Day	comedy	92	7

6 行

tickets

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

8 行

movies

id	title	genre	minutes	rating
1	Robot Summer	family	95	8
2	Dark Lake	horror	110	6
3	Space Pals	family	88	7
4	The Long Road	drama	142	9
5	Laugh Track	comedy	101	5
6	Snow Day	comedy	92	7

6 行

tickets

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

8 行

query.sql

SELECT movies.title,
  -- 在这里写：把 SUM(tickets.seats) 包进 COALESCE(…, 0)
  SUM(tickets.seats)
FROM movies
LEFT JOIN tickets ON tickets.movie_id = movies.id
GROUP BY movies.title;

Ctrl + Enter 也能运行

结果

写好了先点「运行」看看结果，没问题再点「提交」。

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90

id	movie_id	day	seats	price
1	1	Fri	2	40
2	1	Sat	4	80
3	2	Fri	1	25
4	3	Sat	3	54
5	4	Sun	2	60
6	1	Sun	1	20
7	5	Sat	2	36
8	3	Sun	5	90