postgresql 缺少表的 FROM 子句条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19975755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Missing FROM-clause entry for a table
提问by Aan
I wrote the following SQL statement to get data from two tables gendata& TrainingMatrix:
我编写了以下 SQL 语句来从两个表中获取数据gendata& TrainingMatrix：
SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll, "TrainingName", "Institute")"gendata"."Employee Name","gendata"."Position", "gendata"."Department", "TrainingMatrix".*
FROM "TrainingMatrix" JOIN "gendata" ON "TrainingMatrix".payroll = "gendata".payroll
ORDER BY payroll, "TrainingName", "Institute" ,"TrainingDate" DESC NULLS LAST) AS foo;
It works fine, but I need to filter the records more by:
它工作正常，但我需要通过以下方式过滤记录：
WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0
AND EXTRACT(YEAR FROM "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);
So, the orginal SQL statement will be:
因此，原始 SQL 语句将是：
SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll, "TrainingName", "Institute")"gendata"."Employee Name","gendata"."Position", "gendata"."Department", "TrainingMatrix".*
FROM "TrainingMatrix" JOIN "gendata" ON "TrainingMatrix".payroll = "gendata".payroll
ORDER BY payroll, "TrainingName", "Institute" ,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0
AND EXTRACT(YEAR FROM "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);
But I got this error:
但我收到了这个错误：
ERROR: missing FROM-clause entry for table "TrainingMatrix" LINE 3: ...te" ,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingM...
错误：缺少表“TrainingMatrix”第 3 行的 FROM 子句条目：...te","TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingM...
I am using PostgreSQL. Any advise guys?
我正在使用 PostgreSQL。有什么建议吗？
采纳答案by a_horse_with_no_name
As you have wrapped your actual query into a derived table (the select .. from (...) as foo) your "table" isn't called TrainingMatrixany longer. You need to reference it using the alias you use for the derived table:
由于您已将实际查询包装到派生表 (the select .. from (...) as foo) 中，因此不再调用您的“表” TrainingMatrix。您需要使用用于派生表的别名来引用它：
select *
from (
... you original query ..
) as foo
where foo."ExpiryDate" - current_date <= 0
and extract(year from foo."ExpiryDate") = extract(year from current_date)
Btw: I would recommend you stop using quoted identifiers "ExpiryDate"using case-sensitive names usually gives you more trouble than it's worth.
顺便说一句：我建议您停止使用"ExpiryDate"使用区分大小写名称的带引号的标识符通常会给您带来更多的麻烦。
回答by Erwin Brandstetter
100% what @a_horse already said. Plus a couple more things:
100% @a_horse 已经说过。再加上几件事：
Formatyour query so it's easy to read and understand for humans before you try to debug. Even more so, before you post in a public forum.
Use table aliases, especially with your unfortunate CaMeL-case names to make it easier to read.
Provide your table definitions or at least table-qualifycolumn names in your query, so we have a chance to parse it. Your immediate problem is already fixed in the query below. You would also replace
?.accordingly:t.. alias for"TrainingMatrix"g.. alias forgendata
格式化您的查询，以便在您尝试调试之前易于人们阅读和理解。更重要的是，在您在公共论坛上发帖之前。
使用表别名，尤其是使用不幸的 CaMeL 案例名称，以使其更易于阅读。
在您的查询中提供您的表定义或至少表限定的列名，以便我们有机会解析它。您的直接问题已在下面的查询中得到解决。您还可以相应地替换
?.：t.. 别名"TrainingMatrix"g.. 别名gendata
SELECT *
FROM (
SELECT DISTINCT ON (t.payroll, ?."TrainingName", ?."Institute")
g."Employee Name", g."Position", g."Department", t.*
FROM "TrainingMatrix" t
JOIN gendata g ON g.payroll = t.payroll
ORDER BY t.payroll, ?."TrainingName", ?."Institute"
, ?."TrainingDate" DESC NULLS LAST
) AS foo
WHERE foo."ExpiryDate" - current_date <= 0
AND EXTRACT(YEAR FROM foo."ExpiryDate") = EXTRACT(YEAR FROM current_date);
But there's more.
但还有更多。
Like @a_horse wrote, it's a bad idea to use illegal identifiers that have to be double-quoted all the time. But an identifier with enclosed spacecharacter is even worse:
"Employee Name". That's one step away from home-made SQL-injection.The way your additional filters are phrased is bad for performance.
WHERE "ExpiryDate" - current_date <= 0Is not sargableand therefore can't use a plain index. Leaving that aside, it is also more expensive than it needs to be. Use instead:
WHERE "ExpiryDate" >= current_dateSimilar for your 2nd expression, which should be rewritten to:
WHERE "ExpiryDate" >= date_trunc('year', current_date) AND "ExpiryDate" < date_trunc('year', current_date) + interval '1 year'Combining both, we can strip a redundant expression:
WHERE "ExpiryDate" >= current_date AND "ExpiryDate" < date_trunc('year', current_date) + interval '1 year'Your question is ambiguous. Do you want to apply the additional filter before
DISTINCTor after? Different result.
Assuming beforeDISTINCT, you don't need a subquery - which removes the cause for your immediate problem: No different alias for the subquery.
就像@a_horse 写的那样，使用必须一直用双引号引起来的非法标识符是一个坏主意。但是带有空格字符的标识符更糟糕：
"Employee Name". 这离自制的 SQL 注入仅一步之遥。附加过滤器的措辞方式对性能不利。
WHERE "ExpiryDate" - current_date <= 0不可sargable，因此不能使用普通索引。撇开这一点不谈，它也比需要的贵。改用：
WHERE "ExpiryDate" >= current_date类似于您的第二个表达式，应该重写为：
WHERE "ExpiryDate" >= date_trunc('year', current_date) AND "ExpiryDate" < date_trunc('year', current_date) + interval '1 year'结合两者，我们可以去除冗余表达式：
WHERE "ExpiryDate" >= current_date AND "ExpiryDate" < date_trunc('year', current_date) + interval '1 year'你的问题模棱两可。您想在之前
DISTINCT还是之后应用附加过滤器？结果不同。
假设beforeDISTINCT，您不需要子查询 - 这消除了您的直接问题的原因：子查询没有不同的别名。
All together:
全部一起：
SELECT DISTINCT ON (t.payroll, "TrainingName", "Institute")
g."Employee Name", g."Position", g."Department", t.*
FROM "TrainingMatrix" t
JOIN gendata g USING (payroll)
WHERE t."ExpiryDate" >= current_date
AND t."ExpiryDate" < date_trunc('year', current_date) + interval '1 year'
ORDER BY t.payroll, "TrainingName", "Institute", "TrainingDate" DESC NULLS LAST
