错误:缺少表"movies“的FROM-子句条目

Question

我是SQL的新手，需要查询数据库以提取某些信息，然后才能将其导入到另一个我熟悉的软件中分析数据。这个查询是我的一个朋友发送给我的，我现在还不能访问，我不知道为什么它会给我以下错误：

ERROR:  missing FROM-clause entry for table "movies"
LINE 8: FROM (SELECT movies.movieid

下面是查询：

SELECT innerselect.movieid
       ,innerselect.title
       ,innerselect.year
       ,innerselect.imdbid
       ,innerselect.budget[1] AS budget_currency
       ,TO_NUMBER(innerselect.budget[2], '999999999999990.00') AS budget_total
       ,innerselect.businesstext
FROM (SELECT movies.movieid
       ,movies.title
       ,movies.year
       ,movies.imdbid
       ,business.businesstext
       ,regexp_matches(business.businesstext, '^BT:[ ](USD)[ ](-?(?!0)(?:\d+|\d{1,3}(?:,\d{3})+))', 'g') AS budget -- creates a PostgreSQL Array which contains the content matched with the RegEx Groups FROM movies LEFT JOIN business ON movies.movieid=business.movieid WHERE movies.movieid > 2753500
) AS innerselect

任何帮助都将不胜感激。

Answer 1

问题是您将FROM与注释放在同一行，所以FROM子句被忽略了。

SELECT innerselect.movieid
       ,innerselect.title
       ,innerselect.year
       ,innerselect.imdbid
       ,innerselect.budget[1] AS budget_currency
       ,TO_NUMBER(innerselect.budget[2], '999999999999990.00') AS budget_total
       ,innerselect.businesstext
FROM (SELECT movies.movieid
       ,movies.title
       ,movies.year
       ,movies.imdbid
       ,business.businesstext
       ,regexp_matches(business.businesstext, '^BT:[ ](USD)[ ](-?(?!0)(?:\d+|\d{1,3}(?:,\d{3})+))', 'g') AS budget -- creates a PostgreSQL Array which contains the content matched with the RegEx Groups
      FROM movies LEFT JOIN business ON movies.movieid=business.movieid WHERE movies.movieid > 2753500
) AS innerselect