sql - 演员的 Shahrukh 数是“co-acting”图中演员与 Shahrukh Khan 之间的最短路径的长度

Question

问题：演员的沙鲁克数是“合作”图中演员和沙鲁克汗之间的最短路径的长度。也就是说，Shahrukh Khan 的 Shahrukh 编号为 0；与沙鲁克在同一部电影中演出的所有演员都有沙鲁克 1 号；与某些演员在同一部电影中演出的所有演员

答：对于给定的问题，我编写了以下查询，但是输出的结果形状是 (28081, 1)，但预期是 (25698, 1)。

请帮助我了解我哪里出错了。

WITH 
SHAHRUKH_0 AS
(
    SELECT
        TRIM(P.PID) PID
    FROM
        Person P
    WHERE
        Trim(P.Name) like '%Shah%rukh%Khan%'
),
SHAHRUKH_1_MOVIES AS
(
    SELECT
        DISTINCT
        TRIM(MC.MID) MID,
        S0.PID
    FROM
        M_Cast MC,
        SHAHRUKH_0 S0
    WHERE
        TRIM(MC.PID) = S0.PID
),
SHAHRUKH_1_ACTORS AS
(
    SELECT
        DISTINCT
        TRIM(MC.PID) PID
    FROM
        M_Cast MC,
        SHAHRUKH_1_MOVIES S1M
    WHERE
        TRIM(MC.MID) = S1M.MID AND
        TRIM(MC.PID) <> S1M.PID
),
SHAHRUKH_2_MOVIES AS
(
    SELECT
        DISTINCT
        TRIM(MC.MID) MID,
        S1A.PID
    FROM
        M_Cast MC,
        SHAHRUKH_1_ACTORS S1A
    WHERE
        TRIM(MC.PID) = S1A.PID
)
SELECT
    DISTINCT
    TRIM(MC.PID) PID,
    TRIM(P.Name) ACTOR_NAME
FROM
    Person P,
    M_Cast MC,
    SHAHRUKH_2_MOVIES S2M
WHERE
        TRIM(MC.PID) = TRIM(P.PID) AND
        TRIM(MC.MID) = S2M.MID AND
        TRIM(MC.PID) <> S2M.PID

标签： sqlimdb

Answer 1

请发送IMDB数据集SQL查询的正确答案