求评分>3的演员参演电影最大评分差值的SQL查询求助

你的原SQL逻辑存在核心错误：WHERE子句中用ratings.movie_id（电影ID）去匹配一个评分差值（数值），类型完全不匹配；同时子查询计算的是全局所有评分>3电影的最大最小差，而非每个演员自身参演电影的评分差值，这必然得不到预期结果。

正确SQL写法（支持CTE的数据库，如PostgreSQL、MySQL 8+等） #

WITH actor_score_diff AS (
    SELECT 
        s.person_id,
        MAX(r.rating) - MIN(r.rating) AS score_diff
    FROM stars s
    JOIN ratings r ON s.movie_id = r.movie_id
    WHERE r.rating > 3
    GROUP BY s.person_id
)
SELECT person_id, score_diff
FROM actor_score_diff
WHERE score_diff = (SELECT MAX(score_diff) FROM actor_score_diff);

兼容老版本数据库的写法（无CTE支持） #

SELECT 
    s.person_id,
    MAX(r.rating) - MIN(r.rating) AS score_diff
FROM stars s
JOIN ratings r ON s.movie_id = r.movie_id
WHERE r.rating > 3
GROUP BY s.person_id
HAVING score_diff = (
    SELECT MAX(score_diff)
    FROM (
        SELECT MAX(r2.rating) - MIN(r2.rating) AS score_diff
        FROM stars s2
        JOIN ratings r2 ON s2.movie_id = r2.movie_id
        WHERE r2.rating > 3
        GROUP BY s2.person_id
    ) AS temp
);

关键逻辑说明 #

• 先通过关联stars和ratings表，过滤掉评分≤3的无效记录
• 按演员ID分组，计算每个演员参演符合条件电影的评分差值（最高评分-最低评分）
• 最后筛选出差值最大的演员（若有多个演员差值相同且为最大值，会全部返回）

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