错误代码: 1054。' in /ALL/ANY subquery‘中的未知列'movie_id’
错误代码: 1054。' in /ALL/ANY subquery‘中的未知列'movie_id’
提问于 2018-09-27 06:48:37
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 subquery”中不断得到未知列“movie_id”的错误；这是我不理解的，因为这段代码中单独获取的代码块工作得很好。
这里我漏掉了什么？
谢谢！
回答 1
Stack Overflow用户
发布于 2018-09-27 06:57:55
我在这个简化的查询示例中看到了错误：
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 ...)“此列未被选中。
此外，有这么多的合并查询，我相信这可以简化，如果你给出一个例子“带单词”的你想要得到的。
对注释的目标的响应
我没有为你的目标找到一个简单的答案，但我会这样做：
首先，我将根据演员表演的电影创建一个带有演员关系的视图...
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
);现在，如果演员NAME1和演员NAME2参与了同一部电影，那么在前一个视图中将有包含下一个值的行：
(id_name1, name1, movie_id, movie_title, id_name2, name2)
(id_name2, name2, movie_id, movie_title, id_name1, name1)换句话说，关系将出现两次，但这简化了下一个查询...
现在，根据您的定义，可以这样获得演员“凯文·培根”(与他合作过的演员)的1º度贴近度：
CREATE VIEW vw_1_degree_to_kb AS (
SELECT
actor1_id, actor1_fullname
FROM
vw_relations
WHERE
actor2_fullname = "Kevin Bacon"
);现在，对于，演员“凯文·培根”(与他合作过的演员)的2º度的贴近度，我将这样做(也保存在视图中)：
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"
);换句话说，这个视图包含了与“凯文·培根”中接近程度为1的演员合作的演员，但这些演员还不属于那个集合。
更重要的是，的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"
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52527018
复制相关文章
相似问题
腾讯云开发者
