如何查询无共同参演电影类型且类型总数≥7的演员对

嘿，我来帮你搞定这个SQL查询的问题！你现在需要找出满足两个条件的演员对：完全没有共同参演电影类型，且两人各自参演的类型总数之和≥7，但原查询出现了有共同类型的结果，咱们来一步步分析问题并修正。

原SQL的问题分析 #

你的原查询用了多表JOIN，然后加了g1.genre_id!=g2.genre_id的条件，但这个逻辑有明显漏洞：

• 多表JOIN会产生大量笛卡尔积（比如演员A有3种类型，演员B有4种类型，会生成12条关联记录）
• g1.genre_id!=g2.genre_id只是排除了某一对类型相同的记录，但只要存在一对不同的类型组合，这个演员对就会被选中——完全没考虑他们可能有其他共同类型
• 另外，GROUP BY后的统计逻辑虽然能计算类型总数，但因为JOIN的笛卡尔积，统计结果可能也不准确

正确的查询思路 #

我们需要先为每个演员整理出他们参演的所有独特类型集合和类型数量，再基于这个集合去判断两个演员是否完全无交集，同时满足总数之和≥7的条件。

方案1：使用CTE+NOT EXISTS（通用兼容多数数据库） #

先通过CTE（公共表表达式）把每个演员的类型信息整理好，再用NOT EXISTS检查两个演员是否有共同类型：

WITH actor_genres AS (
    SELECT 
        a.actor_id,
        -- 统计该演员参演的独特类型数量
        COUNT(DISTINCT mg.genre_id) AS genre_count,
        -- 把该演员的所有类型ID拼接成字符串，方便后续检查
        GROUP_CONCAT(DISTINCT mg.genre_id ORDER BY mg.genre_id) AS genre_ids
    FROM actor a
    JOIN role r ON a.actor_id = r.actor_id
    JOIN movie_has_genre mg ON r.movie_id = mg.movie_id
    GROUP BY a.actor_id
)
SELECT 
    ag1.actor_id AS i8opoios1,
    ag2.actor_id AS i8opoios2
FROM actor_genres ag1
-- 用!=避免重复对（如果不需要避免A-B和B-A重复，就保留这个条件；如果要去重，改成ag1.actor_id < ag2.actor_id）
JOIN actor_genres ag2 ON ag1.actor_id != ag2.actor_id
WHERE 
    -- 核心条件：检查演员ag2的所有类型都不在ag1的类型列表里，确保无交集
    NOT EXISTS (
        SELECT 1
        FROM movie_has_genre mg
        JOIN role r ON mg.movie_id = r.movie_id
        WHERE r.actor_id = ag2.actor_id
        AND FIND_IN_SET(mg.genre_id, ag1.genre_ids) > 0
    )
    -- 满足类型总数之和≥7
    AND (ag1.genre_count + ag2.genre_count) >= 7
    -- 指定要查询的目标演员（比如你指定的3226）
    AND ag1.actor_id = '3226'
GROUP BY ag1.actor_id, ag2.actor_id;

方案2：使用数组交集判断（适合PostgreSQL等支持数组的数据库） #

如果你的数据库支持数组操作（比如PostgreSQL），可以用更简洁的方式判断类型集合是否无交集：

WITH actor_genres AS (
    SELECT 
        a.actor_id,
        -- 把类型ID聚合为数组
        ARRAY_AGG(DISTINCT mg.genre_id) AS genre_array,
        COUNT(DISTINCT mg.genre_id) AS genre_count
    FROM actor a
    JOIN role r ON a.actor_id = r.actor_id
    JOIN movie_has_genre mg ON r.movie_id = mg.movie_id
    GROUP BY a.actor_id
)
SELECT 
    ag1.actor_id AS i8opoios1,
    ag2.actor_id AS i8opoios2
FROM actor_genres ag1
JOIN actor_genres ag2 ON ag1.actor_id != ag2.actor_id
WHERE 
    -- 用&&操作符判断两个数组是否无交集（FALSE表示无交集）
    ag1.genre_array && ag2.genre_array = FALSE
    AND (ag1.genre_count + ag2.genre_count) >= 7
    AND ag1.actor_id = '3226';

关键注意点 #

• 如果使用MySQL的GROUP_CONCAT，要注意默认的长度限制，如果你的类型数量较多，可以先执行SET group_concat_max_len = 10240;调整长度
• 用ag1.actor_id < ag2.actor_id替代!=可以避免重复的演员对（比如避免同时出现(3226, 123)和(123, 3226)）

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