缺少表的FROM子句条目

Question

我写了以下 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;

它可以正常工作，但我需要更多地通过筛选记录来实现：

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, "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);

然而，我遇到了这个错误：

ERROR: 缺少表 "TrainingMatrix" 的 FROM 条目 在第三行: ...te" ,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingM...

我正在使用PostgreSQL。有什么建议吗？

- Aan

Answer 1

完全同意@a_horse所说的内容。此外，还有以下几点需要注意：

• 在调试之前，将查询格式化使其易于人类阅读和理解。更重要的是，在公共论坛上发帖之前。

• 使用表别名，特别是针对你不幸的驼峰命名方式，这样可以使查询更易读。

• 在查询中提供你的表定义或至少在表中限定列名，这样我们就有机会解析它。下面的查询已经修复了你目前的问题。还需相应地替换?.：

  • 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);

但这还不是全部。

• 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 space character 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 <= 0 
  

  Is not sargable and 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_date
  

  Similar 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 DISTINCT or after? Different result.
  Assuming before DISTINCT, you don't need a subquery - which removes the cause for your immediate problem: No different alias for the subquery.

总之：

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

- Erwin Brandstetter

Answer 2

由于您将实际查询包装到一个派生表中（即 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"，因为区分大小写的名称通常会给你带来更多的麻烦，不值得这么做。

- user330315