无效的标识符 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
相关文章