如何查询与Yash Chopra合作电影数多于其他导演的所有演员

嘿，我太懂你被这个SQL难题卡十多天的憋屈感了——这种“比任何其他导演合作都多”的逻辑，确实容易在分组和对比里绕晕。先帮你理清核心问题，再给你几个可行的解决方向：

你的现有代码已经拿到了演员和Yash的合作次数，以及演员的总电影数，但总电影数≠和单个导演的合作数——这是关键偏差。我们需要的是每个演员和每一位导演的合作次数，再验证Yash的次数是否比其他所有导演都高。

窗口函数是处理这类“分组排名”问题的利器，步骤清晰且性能较好：

1. 先计算每个演员和每个导演的合作次数
2. 给每个演员的导演合作次数按降序排名
3. 筛选出Yash是排名第一，且没有其他导演和他并列的演员

代码示例：

WITH actor_director_counts AS (
    SELECT 
        TRIM(c.PID) AS actor_pid,
        TRIM(d.PID) AS director_pid,
        COUNT(*) AS collaboration_count
    FROM M_Cast c
    JOIN M_Director dir ON TRIM(c.MID) = TRIM(dir.MID)
    JOIN Person d ON TRIM(dir.PID) = TRIM(d.PID)
    GROUP BY actor_pid, director_pid
),
yash_pid AS (
    SELECT TRIM(PID) AS pid FROM Person WHERE Name LIKE '%Yash Chopra%'
),
actor_director_ranked AS (
    SELECT 
        adc.actor_pid,
        adc.director_pid,
        adc.collaboration_count,
        -- 按演员分组，合作次数降序排名
        RANK() OVER (PARTITION BY adc.actor_pid ORDER BY adc.collaboration_count DESC) AS rank_num
    FROM actor_director_counts adc
)
SELECT DISTINCT adr.actor_pid
FROM actor_director_ranked adr
JOIN yash_pid y ON adr.director_pid = y.pid
WHERE adr.rank_num = 1
-- 排除有其他导演和Yash合作次数相同的情况
AND NOT EXISTS (
    SELECT 1
    FROM actor_director_ranked adr2
    WHERE adr2.actor_pid = adr.actor_pid
    AND adr2.director_pid != y.pid
    AND adr2.collaboration_count = adr.collaboration_count
);

如果你的SQL环境不支持窗口函数，可以用嵌套分组和子查询实现相同逻辑：

1. 先获取Yash的PID
2. 计算每个演员和Yash的合作次数
3. 计算每个演员和其他导演合作次数的最大值
4. 对比Yash的次数是否大于这个最大值

代码示例：

WITH yash_pid AS (
    SELECT TRIM(PID) AS pid FROM Person WHERE Name LIKE '%Yash Chopra%'
),
actor_yash_counts AS (
    SELECT 
        TRIM(c.PID) AS actor_pid,
        COUNT(*) AS yash_count
    FROM M_Cast c
    JOIN M_Director dir ON TRIM(c.MID) = TRIM(dir.MID)
    JOIN yash_pid y ON TRIM(dir.PID) = y.pid
    GROUP BY actor_pid
),
actor_max_other_counts AS (
    SELECT 
        TRIM(c.PID) AS actor_pid,
        MAX(dir_count) AS max_other_count
    FROM (
        SELECT 
            TRIM(c.PID) AS actor_pid,
            COUNT(*) AS dir_count
        FROM M_Cast c
        JOIN M_Director dir ON TRIM(c.MID) = TRIM(dir.MID)
        JOIN yash_pid y ON TRIM(dir.PID) != y.pid
        GROUP BY actor_pid, TRIM(dir.PID)
    ) AS other_dir_counts
    GROUP BY actor_pid
)
SELECT ayc.actor_pid
FROM actor_yash_counts ayc
JOIN actor_max_other_counts amoc ON ayc.actor_pid = amoc.actor_pid
WHERE ayc.yash_count > amoc.max_other_count;

• TRIM()的使用：如果你的数据字段没有多余空格，可以去掉TRIM简化查询，提升效率。
• 并列情况处理：如果题目允许“和Yash合作次数等于其他导演最高次数”的演员，把条件里的>改成>=，同时去掉窗口函数方案中的NOT EXISTS部分即可。
• 性能优化：尽量提前获取Yash的PID，避免多次嵌套查询同一个Person表。

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