mysql - 使用连接从 SQL 中的 2 个表中检索一行

Question

SELECT * FROM movie_time AS a,movie_db AS b, movie_actor AS c
WHERE a.Movie_ID = b.Movie_ID AND b.Movie_ID=c.Movie_ID 
      AND c.Movie_ID = a.Movie_ID AND Movie_ID =17

您好，我正在尝试使用连接函数来检索有效的行除非当我放置AND Movie_ID =17 子句时我无法检索对应于 Movie_ID =17

的行

我正在使用 mySQL 工作台。我是否也可以使用类似的语句来删除检索到的特定行？

错误:错误代码 1052 列 'Movie_ID' in where 子句不明确

Answer 1

您需要指定别名a.Movie_ID =17:

SELECT * FROM movie_time AS a,movie_db AS b, movie_actor AS c
WHERE a.Movie_ID = b.Movie_ID AND b.Movie_ID=c.Movie_ID 
      AND c.Movie_ID = a.Movie_ID AND a.Movie_ID =17

对于删除(假设您要从 movie_time 表中删除):

DELETE
    a
FROM 
    movie_time AS a 
INNER JOIN movie_db AS b
    ON a.Movie_ID = b.Movie_ID
INNER JOIN movie_actor AS c
    ON c.Movie_ID = a.Movie_ID
WHERE 
    a.Movie_ID =17