sql三表关联查询_[数库练习]丧病刷SQL·sqlzoo笔记六篇

点进本篇并阅读你可以收获：

• [sql在线练习平台 "sqlzoo"]
• [sql进阶 嵌套子查询命令]
• [sql进阶 多表嵌套联查]
• [sql 常见关键词]（本篇包含'like','count','having'等关键字语句及讲解）

Opening

笔者拖更一段时间，终于还是在周日完成了文章大部分内容，其实忙也还好。

主要是告别了上一家工作，投身于新的工作，所以适应新公司有些耗精力。

总算过来了，薪资提升了50%。

说来惭愧，挺多同辈人都是翻倍100%的涨幅。

只能在角落里继续丧病的刷题，提升自己的能力。

继续在黑暗中磨尖自己的'利齿'，

暗中观察，伺机而出动，一击中的。

那么闲话还是少说，

点开文章的朋友也和我一起加油吧~

Previous Articles

往期文章(如果看到这篇有不懂的地方建议先刷往期文章)：

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记五篇

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记四篇

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记三篇（上）

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记三篇（下）

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记二篇

Crow·Lu：[数库练习]丧病刷SQL·sqlzoo笔记一篇

Practice

1 List the films where the yr is 1962 [Show id, title]

列出1962年的电影id和title

select id,title from movie where yr = 1962

2 Give year of 'Citizen Kane'.

给出title 带有'Citizen Kane' 的年份

select yr from movie
where title like 'Citizen Kane'

3. List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

列出所有的星际迷航电影，包括id，title和year

（所有这些电影的标题中都有星际迷航的字样）。

按yr来排序结果

select id, title, yr from movie
where title like 'Star Trek%'
order by yr

条件写个like star trek 开头

复习一下like 和 %符号吧

4 What id number does the actor 'Glenn Close' have?

Glenn Close' 的演员id是什么

select id from actor
where name = 'Glenn Close'

5 What is the id of the film 'Casablanca'

'Casablanca' 的电影id是什么

select id from movie
where title = 'Casablanca'

6. Obtain the cast list for 'Casablanca'.

列出电影 'Casablanca' 的演员名单。

what is a cast list?
Use movieid=11768, (or whatever value you got from the previous question)

这题给出了 movieid 我们select 一下

select * from casting
where movieid = 11768

可以看到表结构，movieid，actorid和ord

ord这字段是啥暂时先不用管

actorid 字段是这题的关键

我们用 actor.id 和casting.actorid 关联查询

select * from 
casting ct inner join actor ac 
on (ct.actorid = ac.id)
where movieid = 11768

可以看到 关联表结构都出来了，

第六题表结构

只剩下修改一个select name

select name from 
casting ct inner join actor ac 
on (ct.actorid = ac.id)
where movieid = 11768

correct answer

ok, 拿下

7. Obtain the cast list for the film 'Alien'

获取外星人的 演员名单

这题还是关联查询，不过是要3表联查了

在联查前先看一眼alien

select * from movie
where title = 'Alien'

select * from moviewhere title = 'Alien'

我们需要alien电影的演员表

因为演员表不能直接用影片名查检索，

需要先让movie表关联actor表，在让这个关联表去关联 casting表

所以是开头说的三表联查

select * 
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)

三遍联查

这是三表联查展示出来的字段

下一步就是加条件，

select ac.name,也就是select actor.name

然后wehre movie.title = 'Alien'

8. List the films in which 'Harrison Ford' has appeared

列出 'Harrison Ford' 出演过的电影

这个 列出某演员 演过的电影，

需要一张 演员名跟电影名关联的表。

但是我们有的三张表是雪花模型，

做这个查询还是要三表联查。

这题直接复习上一题的三表联查吧，

不熟的同学可以再试一次那段代码

然后就是对这段三表联查添加条件。

查什么？出演的电影，

那就是 电影名 title

select mv.title

条件呢？

'Harrison Ford' 出演过的

where ac.name = 'Harrison Ford'

select mv.title 
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where ac.name = 'Harrison Ford'

这样就可以了，拿下

9. List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

列出 'Harrison Ford' 出演过的电影，且不是主演角色

[]内容：casting 有个ord 字段，这个字段是演员位置

如果ord是1，那这行里该演员就是starring role (主演)

嗯，在上一个代码继续修改吧，

主要思路就是where加条件，加and

我记得不等于是!=，好像挺多代码里都是这么写不等于。

先来看一眼情况吧，

select mv.title, ac.name, ca.ord
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Harrison Ford'

这可以看到结果里，ord里有18的有1的有6的啥数都有。

我们要做的就是剔除ord = 1的

 select mv.title, ac.name, ca.ord
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Harrison Ford'
and
ord != 1

加上ord != 我们就可以看到上一个结果里ord = 1的没了

接下来要做什么？

Wrong answer. Too many columns

okok，改下select ，

删掉多余的ac.name, ca.ord

select mv.title
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Harrison Ford'
and
ord != 1

笑脸弹出， 拿下!

10 List the films together with the leading star for all 1962 films.

列出1962年所有电影的主演名单

这，其实这个雪花模型下的表结构，

只要看到他说要主演名单，然后yr字段又在movie表里

基本就看明白还是三表联查。

select mv.title, ac.name
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where mv.yr = 1962 

然后显示wrong我挺意外的，

点了几次还是没对看了一眼，

下面的 answer should be

A Kind of Loving只有一个 演员Alan Bates

我就在select里加了 ca.ord

select mv.title, ac.name,ca.ord
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where mv.yr = 1962 

我看了一下，这个电影A Kind of Loving演员表里

只有Alan Bates ord是1，也就是这题真正要的是主演名单。

噗噗,我的..我的..

我的锅，

我的审题 问题...

select mv.title, ac.name
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
mv.yr = 1962 
and
ca.ord = 1

拿下。

11. Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

哪年是'Rock Hudson'忙碌的年份

展示出年份及该年电影数量，条件为 两部电影以上

额，还是联查，这次不但是三表联查还要分组，还得计count

select mv.yr, count(ac.name)
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where ac.name = 'Rock Hudson'
group by mv.yr
order by mv.yr

大概就是这样的一个分组语句吧，

但是这个网址没弹笑脸，

意识就是说这不算正确答案。

看了一眼 answer should be

要只输出count>2的

select mv.yr, count(ac.name) 
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Rock Hudson'
group by mv.yr
having count(ac.name) > 2
order by mv.yr

对于 分组筛选完的结果，

我们这个时候要用having了

这里简述一下sql里where 和having的区别

where是先筛选再对数据做分组统计，

having是先分组统计，再对结果进行筛选。

这里我们上一段代码就是对输出的结果再进行筛选count列大于2的值。

having reference

https://www.w3school.com.cn/sql/sql_having.asp

ok拿下~

12. List the film title and the leading actor for all of the films 'Julie Andrews' played in.

要求所有电影中有 'Julie Andrews'出演电影筛选列出 film title 和 the leading actor

这个leading actor 其实挺耐人寻味的，应该是主演吧。

为了对照我用下面这段代码和answer should be对比了一下

select mv.title, ac.name, mv.id, ca.ord
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Julie Andrews'

代码结果

Show what the answer should be...

Darling Lili 和 Duet for One 都是 Julie Andrews的ord = 1

那这个逻辑就可以确定了。

要输出 'Julie Andrews' 参演的电影title还有电影中ord是1的主演

现在我们要用上面代码里 获取的movieid 来检索电影里ord = 1的演员列表

那这就是一个带三表联查的子查询语句

select mv.title,ac.name from
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ca.ord = 1
and
mv.id in 
(
select mv.id
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where 
ac.name = 'Julie Andrews'
)

嗯，好吧，虽然拿下了，

但写完了看了看这段sql代码

感觉今天又是丧病值拉满的一天呢~

13. Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

按字母顺序列出 ， 至少有15个主要的演员名单。

嗯？。。。

这啥啊。。。

重新翻译翻译

列出 哪些演员演过15部主角戏，列表按字母排序

select name
from actor
where id in
(
select actorid from casting 
where ord=1
group by actorid
having count(actorid) >= 15
)
order by name

这题就不多发表啥评论了，

我就不该相信百度翻译。

是个朴实而没有张力的答案。

14. List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

列出1978年上映的电影，按演员数量排序，然后按照title 排序

这题的基础就是这个

select title from movie
where yr = 1978

然后再此查询基础修改

需要count 演员数量排序，

再就是电影名排序。

select mv.title,count(ca.actorid) as countid
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where yr = '1978'
group by mv.title
order by countid desc,mv.title desc

唔，没弹笑脸，

微调一下 order by 条件

注意的是 想要 让count 结果来做排序 要把它用as声明

select mv.title,count(ca.actorid) as countid
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where yr = '1978'
group by mv.title
order by countid desc, mv.title

ok ,拿下

15. List all the people who have worked with 'Art Garfunkel'.

列出所有与 'Art Garfunkel' 合作过的人。

这题，还是三表联查，找出谁和这个人合作就得先把这个人演过的作品select 出来

select mv.id, mv.title, ac.name, ca.ord
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where ac.name = 'Art Garfunkel'

看了一眼结果还好不多，就三部电影

我们接下来就要用这部分代码输出的mv.id来做子查询

select ac.name from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id) 
where mv.id in 
(
select mv.id
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where ac.name = 'Art Garfunkel'
)

但是这还不算完，因为我们这次做的子查询列表里还带'Art Garfunkel'这个人。

select ac.name from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id) 
where 
mv.id in 
(
select mv.id
from 
(movie mv left join casting ca
on (mv.id = ca.movieid))
inner join actor ac 
on (ca.actorid = ac.id)
where ac.name = 'Art Garfunkel'
)
and
name != 'Art Garfunkel'

来~力度拉满！龙卷风摧毁停车场！

Thanks

关于本篇"丧病刷SQL"，

其实目前我写数据文章还有很多不足，

在这里提前感谢未来的读者提供勘误。

勘误校正：

可以在下面的评论区指出或者直接知乎私信留言。

在此提前谢谢未来勘误的朋友啦~

最后感谢：@Rocky0429

感谢你文章分享的sqlzoo，

Rocky0429：在线就能用的 SQL 练习平台我给你找好了！

真的是很好玩很好用好练习的网站，再次感谢！

Epilogue

其实写到这里还是有别的话想说，

虽然一篇就写过了：

刷题的各位有没有想过自己刷题的意义是什么？

[自己要走的路]要靠自己的意志来决定
—— Bruno Bucciarati 《JoJo's Bizarre Adventure》

我的答案是，为了更接近并实现自己的梦想。

Ending

希望看文章看到最后的朋友都能给予

点赞，喜欢，收藏，转发支持，

上述支持讲对应收到以下祝福：

跳槽、加薪、转行、晋升成功。

那么，本篇力度拉满的

[数库练习]丧病刷SQL·sqlzoo笔记六篇

题例 'more join operations'全15题，

完~