如何结合Max()与Join实现按自定义评分规则排序关联表数据？

没问题，这个自定义排序需求可以通过分步计算各维度最高分再求和来实现，我给你写一套清晰的SQL方案，正好匹配你的预期结果：

WITH filtered_movies AS (
    -- 替换成你已经筛选出符合用户偏好的电影的查询语句
    SELECT movie_id, movie_name FROM general_info WHERE movie_name IN ('Batman', 'Iron Man')
),
max_cast_scores AS (
    SELECT 
        c.movie_id,
        MAX(ucp.score) AS max_cast_score
    FROM cast c
    JOIN user_cast_preference ucp ON c.cast_id = ucp.cast_id
    JOIN filtered_movies fm ON c.movie_id = fm.movie_id
    GROUP BY c.movie_id
),
max_prod_scores AS (
    SELECT 
        pc.movie_id,
        MAX(upp.score) AS max_prod_score
    FROM production_companies pc
    JOIN user_production_preference upp ON pc.production_company_id = upp.production_company_id
    JOIN filtered_movies fm ON pc.movie_id = fm.movie_id
    GROUP BY pc.movie_id
)
SELECT 
    fm.movie_name,
    COALESCE(mcs.max_cast_score, 0) + COALESCE(mps.max_prod_score, 0) AS total_preference_score
FROM filtered_movies fm
LEFT JOIN max_cast_scores mcs ON fm.movie_id = mcs.movie_id
LEFT JOIN max_prod_scores mps ON fm.movie_id = mps.movie_id
ORDER BY total_preference_score DESC;

代码逻辑解释 #

• filtered_movies：用来存放你已经筛选好的符合用户偏好的电影集合（这里用IN语句模拟了你的结果，实际使用时替换成你的原始筛选查询即可）
• max_cast_scores：关联cast表和用户演员偏好表，按电影ID分组，取出每部电影对应的最高演员偏好评分
• max_prod_scores：同理，关联制片公司表和用户制片偏好表，取出每部电影对应的最高制片公司偏好评分
• 最终查询：把三个数据集关联起来，用COALESCE处理可能的空值（比如某部电影没有对应偏好记录时默认加0），计算总分后按降序排序，就能实现你要的Iron Man(180)在前、Batman(155)在后的效果

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