我编写了以下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”使用区分大小写的名称通常会给你带来更多的麻烦.
