如何编写SQL查询获取与其他电影演员阵容完全相同的影片ID及标题

需求：编写SQL查询，列出所有与其他影片拥有完全相同演员阵容的影片的film_id和title。

原解法问题分析 #

你提供的原查询逻辑上能判断两个影片演员集合是否相等，但存在两个明显问题：

1. 笛卡尔积关联Film R和Film S会产生大量冗余结果，比如影片A和B匹配时，会同时出现(A,B)和(B,A)两组记录；
2. 没有排除影片自身和自身匹配的情况（即R.film_id = S.film_id的记录），这会导致所有影片都被错误地包含进来。

修正后的解法 #

方法一：基于EXCEPT的集合匹配（通用SQL）

通过EXISTS子查询筛选出存在其他影片和自己演员阵容完全一致的影片，同时排除自身匹配：

SELECT R.film_id, R.title
FROM Film R
WHERE EXISTS (
    SELECT 1
    FROM Film S
    WHERE R.film_id <> S.film_id
      -- R的演员集合完全包含于S的演员集合
      AND NOT EXISTS (
          SELECT actor_id FROM Film_Actor WHERE film_id = R.film_id
          EXCEPT
          SELECT actor_id FROM Film_Actor WHERE film_id = S.film_id
      )
      -- S的演员集合完全包含于R的演员集合
      AND NOT EXISTS (
          SELECT actor_id FROM Film_Actor WHERE film_id = S.film_id
          EXCEPT
          SELECT actor_id FROM Film_Actor WHERE film_id = R.film_id
      )
)

方法二：基于演员ID聚合的匹配（数据库特定）

如果你的数据库支持字符串聚合函数（如PostgreSQL的STRING_AGG、MySQL的GROUP_CONCAT），可以先将每个影片的演员ID排序后拼接成字符串，再找出字符串相同的影片：

-- 以PostgreSQL为例，MySQL可替换为GROUP_CONCAT(actor_id ORDER BY actor_id)
WITH FilmActorGroups AS (
    SELECT 
        film_id,
        STRING_AGG(CAST(actor_id AS VARCHAR), ',' ORDER BY actor_id) AS actor_list
    FROM Film_Actor
    GROUP BY film_id
)
SELECT f.film_id, f.title
FROM Film f
JOIN FilmActorGroups fag ON f.film_id = fag.film_id
WHERE EXISTS (
    SELECT 1
    FROM FilmActorGroups fag2
    WHERE fag2.film_id <> fag.film_id
      AND fag2.actor_list = fag.actor_list
)

两种方法对比 #

• 方法一：兼容性强，不依赖数据库特定函数，适合跨数据库场景；
• 方法二：逻辑更直观，性能在中小数据量下表现较好，但需要数据库支持有序聚合的字符串拼接函数。

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