sql – 缺少表的FROM子句条目

发布时间：2020-12-12 06:50:30 所属栏目：MsSql教程来源：网络整理

导读：我编写了以下SQL语句来从两个表中获取数据gendata TrainingMatrix： SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll,"TrainingName","Institute")"gendata"."Employee Name","gendata"."Position","gendata"."Department","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,"Institute","TrainingDate" DESC NULLS LAST) AS foo;

它工作正常,但我需要通过以下方式过滤记录：

WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0 
AND  EXTRACT(YEAR FROM  "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);

因此,原始SQL语句将是：

SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0 
AND  EXTRACT(YEAR FROM  "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);

但是我收到了这个错误：

ERROR: missing FROM-clause entry for table “TrainingMatrix” LINE 3:
…te”,”TrainingDate” DESC NULLS LAST) AS foo WHERE “TrainingM…

我正在使用PostgreSQL.有人建议吗？

解决方法

当您将实际查询包装到派生表(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)

顺便说一句：我建议你停止使用引用的标识符“ExpiryDate”使用区分大小写的名称通常会给你带来更多的麻烦.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!