我如何让所有演员都没有出现在r级电影

9jyewag0 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(289)

我想得到 All actors that haven't appeared in R rated films 这很容易处理 sub-queries ```
select *
from actor
where actor_id not in
(select actor_id
from actor
inner join film_actor using (actor_id)
inner join film using (film_id)
where rating = 'R');

我不能和你一起做这件事 `joins` - 
到现在为止我都是这么做的-

select distinct(a.actor_id), a.first_name, a.last_name
from actor as a
left join
(film_actor as fa
inner join film as f
on fa.film_id = f.film_id)
on a.actor_id = fa.actor_id
where f.rating = 'R'
order by actor_id

在上面的查询中，我得到了所有在 `R` 分级电影，但现在我不知道如何获得 `non R` 分级影片
有人能帮我做这个吗？

sql Join subquery

来源：https://stackoverflow.com/questions/62129267/how-do-i-get-all-actors-that-havent-appeared-in-r-rated-films

2条答案

按热度按时间

x0fgdtte1#

WHERE 子句在联接之后应用。
事实是你的 LEFT JOIN 实际上已经回到了一个 INNER JOIN :
-那个 LEFT JOIN 返回一些带有 NULL s
-那个 WHERE 条款不包括 NULL s
相反，您必须过滤到 R 上映前的电影 JOIN 在演员桌上。

select distinct(a.actor_id), a.first_name, a.last_name
from actor as a
  left join
    (film_actor as fa
       inner join film as f
         on fa.film_id = f.film_id
         and f.rating = 'R')         -- R Rating filter here
  on a.actor_id = fa.actor_id
  where f.film_id IS NULL            -- where the actor does NOT map to such films
  order by actor_id

这个 DISTINCT 也不需要，连接是1个演员到0个电影，所以没有复制。
而且，我觉得你的风格很难理解。试着把东西隔开一点，让缩进的程度保持一致？

SELECT
    a.actor_id,
    a.first_name,
    a.last_name
FROM
  actor   AS a
LEFT JOIN
(
        film_actor   AS fa
    INNER JOIN
        film         AS f
            ON  fa.film_id = f.film_id
            AND f.rating   = 'R'
)
    ON  a.actor_id = fa.actor_id
WHERE
    f.film_id IS NULL
ORDER BY
    a.actor_id

最后， NOT EXISTS 通常比 NOT IN 对于这些情况（反半联接）。

SELECT
    *
FROM
    actor
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            film_actor
        INNER JOIN
            film
                USING (film_id)
        WHERE
                film.rating         = 'R'
            AND film_actor.actor_id = actor.id
    )

赞(0）回复(0）举报 2021-08-13

inn6fuwd2#

所以，如果你知道如何找到r级影片中的所有演员，那么sql减法是一个很好的解决方案。选择所有参与者减去选择r级文件中的所有参与者。

赞(0）回复(0）举报 2021-08-13