无效的标识符 SQL

问题描述

所以我有这个:

SELECT p.plantnaam,o.levcode,o.offerteprijs
FROM plant p, offerte o
JOIN (SELECT plantcode , MIN(offerteprijs) AS offprijs 
      FROM offerte
      GROUP BY plantcode) s
  ON s.plantcode = p.plantcode
  AND s.offprijs = o.offerteprijs
ORDER BY p.plantnaam,l.levcode

显然在第 6 行，p.plantcode 突然神奇地变成了一个无效标识符.为什么是这样?为什么在此之前完全相同的表中的所有其他人都很好?

Appearently on the 6th row, p.plantcode is suddenly magically an invalid identifier. Why is this? and why are all the others from the exact same table perfectly fine before that point?

推荐答案

问题在于您正在混合 JOIN.您有隐式和显式连接.带有 ON 子句的显式 JOIN 语法比带有逗号的隐式连接具有更高的优先级.结果 plant 和 offerte 表的别名在 ON 子句中将不可用.尝试在整个过程中使用相同的 JOIN 类型:

The problem is that you are mixing JOINs. You have both implicit and explicit joins. The explicit JOIN syntax with the ON clause has a higher precedence over the implicit join with the commas. As a result the alias for the plant and the offerte tables will not be available in the ON clause. Try using the same JOIN type throughout:

SELECT p.plantnaam, o.levcode, o.offerteprijs
FROM 
(
  SELECT plantcode , MIN(offerteprijs) AS offprijs 
  FROM offerte
  GROUP BY plantcode
) s
INNER JOIN plant p
   ON s.plantcode = p.plantcode
INNER JOIN offerte o
   ON s.offprijs = o.offerteprijs
ORDER BY p.plantnaam, l.levcode

这篇关于无效的标识符 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！