如何让SQL查询仅返回无共同类型且合并类型数≥7的演员对？

Your original query has two key issues that prevent it from returning the correct results:

1. The condition mg1.genre_id != mg2.genre_id doesn't properly exclude actor pairs that share any common genre (it only filters out individual genre pairs between the two actors, not the entire actor pair if they overlap at all).
2. You're calculating the result flag in the SELECT clause but not filtering the output to only include rows where this flag is TRUE.

Here's the corrected query that addresses both problems:

WITH actor_genres AS (
    -- Get all distinct genres each actor has appeared in
    SELECT 
        a.actor_id,
        g.genre_id
    FROM actor a
    JOIN role r ON a.actor_id = r.actor_id
    JOIN movie m ON r.movie_id = m.movie_id
    JOIN movie_has_genre mg ON m.movie_id = mg.movie_id
    JOIN genre g ON mg.genre_id = g.genre_id
    GROUP BY a.actor_id, g.genre_id
),
actor_genre_counts AS (
    -- Precompute the number of distinct genres per actor
    SELECT 
        actor_id,
        COUNT(genre_id) AS genre_count
    FROM actor_genres
    GROUP BY actor_id
)
SELECT 
    agc1.actor_id AS i8opoios1,
    agc2.actor_id AS i8opoios2,
    (agc1.genre_count + agc2.genre_count) AS total_combined_genres
FROM actor_genre_counts agc1
JOIN actor_genre_counts agc2 ON agc1.actor_id < agc2.actor_id
-- Ensure the two actors have NO common genres
WHERE NOT EXISTS (
    SELECT 1
    FROM actor_genres ag1
    JOIN actor_genres ag2 ON ag1.genre_id = ag2.genre_id
    WHERE ag1.actor_id = agc1.actor_id
      AND ag2.actor_id = agc2.actor_id
)
-- Filter only pairs with combined genre count >=7
AND (agc1.genre_count + agc2.genre_count) >= 7;

Key Changes Explained: #

• CTEs for Clarity & Efficiency: We use two CTEs to break down the problem:
  • actor_genres: Lists every distinct genre each actor has acted in (avoids duplicate genre entries per actor).
  • actor_genre_counts: Calculates how many unique genres each actor has, so we don't recalculate this multiple times.
• Proper Genre Disjoint Check: The NOT EXISTS subquery ensures there are no genres shared between the two actors. This correctly excludes any pair that has even one overlapping genre.
• Direct Filtering: Instead of calculating a result flag, we directly filter pairs where the sum of their genre counts is at least 7 using the AND condition in the WHERE clause (or you could use a HAVING clause if you preferred, but this is more efficient here).

Notes: #

• I noticed a typo in your table structure: genre(genre_id, gender_name) should likely be genre(genre_id, genre_name) (matches your original query's g1.genre_name reference). The query above assumes this correction.
• Using a1.actor_id < a2.actor_id ensures we don't return duplicate pairs (e.g., (1,2) and (2,1)), which your original query already handled correctly.

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