简介
sqlzoo是一个适合用于练习sql的题库网站
不要觉得页面丑而且无简体中文
如果做得完大部分题目比你听网课更有成效
补充:我是菜比
本练习的习题地址
https://sqlzoo.net/wiki/More_JOIN_operations/zh
繁体中文版与英文版题目编号是不同的
Movie-er.png
练习
一些基础的练习跳过了
7. 列出电影 'Casablanca'的演员名单
原习题有三张表,分别是电影,演员,中间表
因此需要多表join的操作
影片表 join 中间表 join 演员表
SELECT name FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE movie.title='Casablanca'
11. 列出1962年首映的电影以及他的主角
这道题本应很简单,但我脑子卡壳了
一直在考虑怎么用子查询来判断是不是主角
直到把结果都显示后才发现中间表有标注是不是主角
SELECT title,name
FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE
yr='1962' and ord=1
12. 列出John Travolta(低俗小说的文森特)最忙的一年,显示年份yr和上映数量
这题答案是明示在输入框内的,主要分析一下怎么思考
- 先想办法列出文森特每年都出多少片
select yr,count(title) from
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
group by yr
- 再想办法弄出最多的那年
已知max(count(num))是非法语句,因此需要通过查询从表来获取max
select max(mycount)
from (
select count(title) mycount
FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
group by yr
) mycount
※ 需要设置好别名mycount,因为mysql规定新的临时表必须要有别名
- 既然得出以上2个结果,所根据再通过
having来组合二者
select yr,count(title) from
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
group by yr
having count(title)=
(
select max(mycount)
from (
select count(title) mycount
FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name='John Travolta'
group by yr
) mycount
)
※ 关于 where 和 having 的区别
where: 约束声明,用于约束从数据库中获取的数据,不可跟聚合函数
having: 过滤声明,用于过滤已经获取的数据,可以跟聚合函数
因此此处使用having来过滤结果
13. 列出'Julie Andrews'参与的电影,和该电影的主角
Julie Andrews不熟,但是我知道史莱克里面公主菲欧娜和卑鄙的我里面格鲁,他们的妈就是她配的
还是分步解析
- 如何找到她的电影
SELECT title FROM casting join movie on movieid = id
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
- 根据这个表可以找到所有电影的主角吗?
将这个表作为临时表来进行查询
select movie.title,actor.name
from
movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
,
(
SELECT title FROM casting join movie on movieid = id
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
) mytable
where
movie.title = mytable.title
and
ord = '1'
但结果发现有2个小麻烦(Little Miss Marker)
根据提示,这电影重版了一次而且她都参与了,因此需要根据临时表中的movieid而不是title来进行匹配
select movie.title,actor.name
from
movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
,
(
SELECT movieid FROM casting join movie on movieid = id
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
) mytable
where
movie.id = mytable.movieid
and
ord ='1'
group by title
记得最后加上group by,因为有部电影她演了俩角色
※ 关于 in 的使用
允许在多行结果中进行匹配,这里的in是题目提示你的,可能出题者想炫技因此用in而不是=
14. 按字母顺序列出出演30次或以上的主角名字
分析:
统计肯定用count(),而主角则是通过ord = '1'来设置
此外,还记得什么后可以跟聚合函数吗?因此
select name from
actor join casting on actor.id = actorid
where ord = '1'
group by name
having count(name)>=30
order by name
15. 列出1978年的电影名称和演员数，按演员数降序和* 标题 *顺序进行排序
繁体中文的题目存在问题,无论你怎么做都得不出正确结果的
如果真想做此题建议去英文版
题目本身没什么难度,依然是聚合函数的应用
select title,count(actorid) from
movie join casting on id = movieid
where
yr = '1978'
group by movieid
order by count(actorid) desc,title
sof有个回答贼搞笑在此题改版前为了匹配正确答案他进行了大量case when的判断....
https://stackoverflow.com/questions/24230460/sqlzoo-more-join-operations-15
15. 列出与'Art Garfunkel'合作过的演员
分析:
- 先获取所有art出演的电影的id
select movieid from
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name = 'Art Garfunkel'
- 再根据这张临时表获取其他参与过该场电影的演员
select name from
(select movieid from
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where name = 'Art Garfunkel')mytable,
casting join actor on id = actorid
where casting.movieid = mytable.movieid
and
name != 'Art Garfunkel'
最后记得不要忘记排除art他自己
另一种用in的思路,源自
https://stackoverflow.com/questions/15466826/sqlzoo-more-join-execerise-16
select actor.name
from casting
join
actor on casting.actorid=actor.id
join
movie on movie.id=casting.movieid
where casting.movieid in
( select movie.id
from actor
join
casting on actor.id=casting.actorid
join
movie on movie.id=casting.movieid
where actor.name='Art Garfunkel'
group by movie.id
)
and actor.name!='Art Garfunkel'
总结
相对子查询,本次和之前的join练习依然比较简单
主要12题开拓了思路,通过聚合和having来实现一些复杂的条件
其次是in的使用,不过我还是习惯通过from子查询的表来过滤条件
网友评论