SQL基础练习：如何实现‘至多合作一部昆汀执导影片’的查询

首先，先指出你现有代码的几个关键问题：

• SQL里没有exists unique这种语法，EXISTS关键字仅用于判断子查询是否返回结果，不能和UNIQUE搭配使用；
• 你的查询没有先筛选出昆汀执导的影片，导致可能混入其他导演的影片数据，结果肯定不对；
• 演员对的生成没有做去重处理，会出现重复的反向对（比如(A,B)和(B,A)都被算作不同的结果，造成冗余）。

核心逻辑转换：如何实现“至多一部影片” #

“至多一部”的本质是统计两个演员共同参演的昆汀影片数量，判断这个数量≤1。我们需要先锁定所有昆汀的影片及其参演演员，再对演员对进行分组统计。

可行的SQL实现方案 #

这里提供两种高效的写法，你可以根据自己的数据库环境选择：

方法一：使用CTE+分组统计（推荐）

这种写法逻辑清晰，效率也不错：

-- 先筛选出所有参演昆汀电影的演员及对应影片
WITH TarantinoFilmsActors AS (
    SELECT 
        f.FilmCode,
        i.Actor
    FROM Film f
    JOIN Interpretation i ON f.FilmCode = i.Film
    WHERE f.Filmmaker = 'Quentin Tarantino'
)
SELECT 
    a1.ActorCode AS Actor1,
    a2.ActorCode AS Actor2,
    -- 可选：显示共同合作的影片数量
    COUNT(DISTINCT t_common.FilmCode) AS CoopFilmCount
FROM Actor a1
JOIN Actor a2 ON a1.ActorCode < a2.ActorCode  -- 避免重复的反向演员对
-- 确保两个演员都参演过昆汀的电影
JOIN TarantinoFilmsActors t1 ON t1.Actor = a1.ActorCode
JOIN TarantinoFilmsActors t2 ON t2.Actor = a2.ActorCode
-- 统计两人共同参演的影片
LEFT JOIN TarantinoFilmsActors t_common 
    ON t_common.FilmCode = t1.FilmCode 
    AND t_common.Actor = a2.ActorCode
GROUP BY a1.ActorCode, a2.ActorCode
HAVING COUNT(DISTINCT t_common.FilmCode) <= 1;

方法二：子查询计数法

如果你不习惯用CTE，也可以用子查询直接统计：

SELECT 
    a1.ActorCode AS Actor1,
    a2.ActorCode AS Actor2
FROM Actor a1
CROSS JOIN Actor a2
WHERE a1.ActorCode < a2.ActorCode
-- 确保演员1参演过昆汀的电影
AND EXISTS (
    SELECT 1 
    FROM Interpretation i
    JOIN Film f ON i.Film = f.FilmCode
    WHERE i.Actor = a1.ActorCode AND f.Filmmaker = 'Quentin Tarantino'
)
-- 确保演员2参演过昆汀的电影
AND EXISTS (
    SELECT 1 
    FROM Interpretation i
    JOIN Film f ON i.Film = f.FilmCode
    WHERE i.Actor = a2.ActorCode AND f.Filmmaker = 'Quentin Tarantino'
)
-- 统计两人共同参演的昆汀影片数量≤1
AND (
    SELECT COUNT(DISTINCT f.FilmCode)
    FROM Film f
    JOIN Interpretation i1 ON f.FilmCode = i1.Film
    JOIN Interpretation i2 ON f.FilmCode = i2.Film
    WHERE i1.Actor = a1.ActorCode 
      AND i2.Actor = a2.ActorCode 
      AND f.Filmmaker = 'Quentin Tarantino'
) <= 1;

关键细节说明 #

• 用a1.ActorCode < a2.ActorCode来避免重复的演员对（比如(A,B)和(B,A)会被视为同一对，只返回一次）；
• 两种方法都先确保了演员对中的两人都至少参演过一部昆汀的电影（如果你的需求包括其中一人没参演过的情况，可以去掉对应的EXISTS或JOIN条件）；
• 统计共同影片数量时用COUNT(DISTINCT)是为了避免同一影片被重复计数（虽然题目里说同一演员在同一影片只饰演一个角色，这里加DISTINCT更严谨）。

内容的提问来源于stack exchange，提问作者Roberto