mysql - 错误代码 : 1054. 'movie_id' 中的未知列 'IN/ALL/ANY subquery'

Question

   SELECT DISTINCT actor_id 
    FROM 
      (SELECT DISTINCT actor_id
      FROM cast 
      WHERE NOT movie_id in 
            (SELECT movie_id 
            FROM cast 
            INNER JOIN actors 
            ON actors.ID = cast.actor_id 
            WHERE full_name = 'Kevin Bacon')) as A
     WHERE movie_id in 
     (SELECT movie_id  
     FROM cast 
     WHERE actor_id in
        (SELECT DISTINCT actor_id 
        FROM cast 
        WHERE movie_id in 
            (SELECT movie_id  
            FROM cast 
            INNER JOIN actors 
            ON actors.ID = cast.actor_id 
            WHERE full_name = 'Kevin Bacon')))
   AND actor_id <> (SELECT id from actors
   where full_name = "Kevin Bacon")
;

我不断收到“IN/ALL/ANY 子查询”中未知列“movie_id”的错误；我不明白，因为单独的代码块工作得很好。

我在这里缺少什么？

谢谢!

Answer 1

我在您的查询的这个简化示例中看到错误:

SELECT DISTINCT actor_id
FROM ( SELECT DISTINCT actor_id FROM ...) as A
WHERE movie_id in (...);

在 WHERE 子句中，您引用“A”表中的“movie_id”，但在内部查询“( SELECT DISTINCT actor_id FROM ...)”中，未选择此列。

此外，还有很多经过处理的查询，我相信如果您给出一个“带有文字”的示例来说明您想要得到的内容，这可以简化。

对评论目标的回应

我没有找到实现您目标的简单答案，但我会这样做:

首先，我将根据类型转换演的电影创建一个与 Actor 关系的 View ...

CREATE VIEW vw_relations AS (
    SELECT
        c1.actor_id AS actor1_id, a1.full_name AS actor1_fullname,
        c1.movie_id, m.title AS movie_title,
        c2.actor_id AS actor2_id, a2.full_name AS actor2_fullname
    FROM
        cast AS c1
    INNER JOIN
        cast AS c2 ON c2.movie_id = c1.movie_id AND c2.actor_id != c1.actor_id
    INNER JOIN
        movies AS m ON m.id = c1.movie_id
    INNER JOIN
        actors AS a1 ON a1.id = c1.actor_id
    INNER JOIN
        actors AS a2 ON a2.id = c2.actor_id
);

现在，如果 Actor NAME1 与 Actor NAME2 参与了同一部电影，则上一个 View 中将出现包含下一个值的行:

(id_name1, name1, movie_id, movie_title, id_name2, name2)
(id_name2, name2, movie_id, movie_title, id_name1, name1)

换句话说，关系将出现两次，但这简化了接下来的查询...

现在，根据您的定义， Actor “Kevin Bacon”(与他合作过的 Actor )的1°亲密度可以这样获得:

CREATE VIEW vw_1_degree_to_kb AS (
    SELECT
        actor1_id, actor1_fullname
    FROM
        vw_relations
    WHERE
        actor2_fullname = "Kevin Bacon"
);

现在，对于 Actor “Kevin Bacon”(与曾与他合作过的 Actor )的2度亲密度，我将这样做(并保存在 View 中):

CREATE VIEW vw_2_degree_to_kb AS (
    SELECT
        actor1_id, actor1_fullname
    FROM
        vw_relations
    WHERE
        actor2_id IN (SELECT actor1_id FROM vw_1_degree_to_kb)
    AND
        actor1_id NOT IN (SELECT actor1_id FROM vw_1_degree_to_kb)
    AND
        actor1_fullname != "Kevin Bacon"
);

换句话说，此 View 包含与“Kevin Bacon”的 1° 亲密度 Actor 合作过的 Actor ，但尚未属于该集合。

更重要的是，3°接近度将是这样的:

CREATE VIEW vw_3_degree_to_kb AS (
    SELECT
        actor1_id, actor1_fullname
    FROM
        vw_relations
    WHERE
        actor2_id IN (SELECT actor1_id FROM vw_2_degree_to_kb)
    AND
        actor1_id NOT IN (SELECT actor1_id FROM vw_1_degree_to_kb)
    AND
        actor1_id NOT IN (SELECT actor1_id FROM vw_2_degree_to_kb)
    AND
        actor1_fullname != "Kevin Bacon"
);