SQL中的关系划分

Question

编写SQL查询，列出在每部电影中以“CHOCOLAT”开头的演员的全名(“”)：

​

​

以下是我的做法：

SELECT 
    actor_id, first_name || ' ' || last_name AS fullname
FROM 
    Actor A
WHERE 
    NOT EXISTS (SELECT film_id
                FROM Film F
                WHERE title LIKE 'CHOCOLAT%'
                EXCEPT
                SELECT film_id 
                FROM Film_Actor FA
                WHERE FA.actor_id = A.actor_id)
ORDER BY 
    fullname

这种方法的问题是，当没有一个标题以“CHOCOLAT%”开头的电影时，它会列出所有演员，而不是返回一个空表。

Answer 1

查询选择匹配的影片，并为每个演员移除演员所执行的电影。如果一部电影仍然存在，演员就不能在电影中表演，所以他不能出现在最后的结果中。

SELECT actor_id,
       first_name || ' ' || last_name AS fullname
FROM Actor A
WHERE NOT EXISTS(SELECT film_id
                 FROM Film F
                 WHERE title LIKE 'CHOCOLAT%' -- select all matching films
                 EXCEPT
                 SELECT film_id
                 FROM Film_Actor FA
                 WHERE FA.actor_id = A.actor_id -- remove the films the actor acts in
) -- have a set of films the actor does not act in
-- if there is no matching film the actor does not act in,
-- (i.e. he acts in all of them) he needs to be selected
  and (SELECT count(film_id)
       FROM Film F
       WHERE title LIKE 'CHOCOLAT%') > 0 
-- special case, when there is no matching film at all: 
-- The list of films the actor does not act in would be empty for every actor
-- Thus, the result would be all actors
ORDER BY fullname;

基本上，您可以添加另一个if-子句，它至少需要一个匹配的电影才能出现。您可以使用count() > 0或exists(...)来完成此操作。