（持续更新）2021-01-19-SQL（SQLZOO More Join）刷题精华部分-HK_select name from movie join casting on movie.id = -CSDN博客

本文链接：https://blog.csdn.net/weixin_42012759/article/details/112305443

SQLZOO-More Join

小目录：

如何连接三个表；
用非主键变量筛选导致的错误；（More Join-12题）
使用group by+having count进行分组统计（More Join-13题）
用分组计数值进行排序（More Join-14题）
洋葱式嵌套-简单暴力的逻辑拆解（More Join-15题）

正文：

如何连接三个表：

把带有其他两个表的连接条件的那个表放在两次LEFT JOIN的中间，作为连接的枢纽；
示例：
其中的表casting包含了表movie和actor的movieid和actorid

select title, name
from movie LEFT JOIN casting ON (movie.id = casting.movieid)
           LEFT JOIN actor ON (actor.id = casting.actorid)
where ord=1
   && yr = 1962

用非主键变量筛选导致的错误

示例：（More Join-12题）
这里的筛选使用了Title，这是一个非主键变量，Title不是唯一的，会有两个内容不同的电影使用同一个Title，故在title IN这一步，错误的Title会借正确的Title之名通过筛选条件而与正确的Title一起被SELECT出来。

SELECT distinct title, name FROM
  movie JOIN casting ON (movie.id=movieid)
        JOIN actor   ON (actorid=actor.id)
WHERE  title IN (select title
                  from  movie JOIN casting ON movie.id=movieid
                              JOIN actor   ON actorid=actor.id
                 where name='Julie Andrews'
)
&& ord = 1

应该使用id作为筛选条件（唯一性）：
这里是movieid作为主键筛选条件

SELECT distinct title, name FROM
  movie JOIN casting ON (movie.id=movieid)
        JOIN actor   ON (actorid=actor.id)
WHERE  movieid IN (select movieid
                    from casting
                   where actorid = (select id 
                                     from actor
                                    where name='Julie Andrews')
)
&& ord = 1

使用group by+having count进行分组统计
对name进行分组，并统计每个name下的ord=1条件的数量。筛选计数条件要用having。

示例：（More Join-13题）

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

用分组计数值进行排序
先对数据分组，再order。分组的对象是这个分组计数值的所有者，如一群actor的所有者是一部movie，故对movieid分组，再对actorid计数，就是分组计数；

示例：（More Join-14题）

select title, count(actorid) a
from casting JOIN movie ON casting.movieid = movie.id
where yr = 1978
group by movie.id, title
order by a DESC, title

洋葱式嵌套-简单暴力的逻辑拆解

示例：（More Join-15题）

逻辑路线：
AG的name -> AG的id -> AG电影的id <- 演员的id <- 演员的name

在逻辑路线的中心连接处“AG电影的id” 使用IN进行对象转换，从AG侧转换到了演员侧；

（缺点是：重复连接了两次actor和casting，肯定有什么优化的办法，暂时没想到）

select distinct name 
from actor JOIN casting ON actor.id = casting.actorid
where  movieid IN (select movieid 
                     from casting JOIN actor ON actor.id = casting.actorid
                    where actorid = (select id 
                                       from actor
                                      where name = 'Art Garfunkel')
)
&& name != 'Art Garfunkel'