ON 子句中的 MySQL 未知列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4065985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL unknown column in ON clause
提问by Matthew James Taylor
I have the following MySQL query:
我有以下 MySQL 查询：
SELECT p.*,
IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
pm.MediaID,
date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
astext(pg.Geometry) AS Geometry
FROM property p, propertygeometry pg
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
AND p.PropertyGeometryID = pg.id
GROUP BY p.id
And I'm getting this error:
我收到此错误：
#1054 - Unknown column 'p.id' in 'on clause'
#1054-“on 子句”中的未知列“p.id”
As far as I can see the query looks right, any idea what could be wrong?
就我所见，查询看起来是正确的，不知道哪里出了问题？
回答by Mark Byers
Don't mix ANSI-89 style and ANSI-92 style joins. They have different precedence which can lead to confusing errors, and that is what has happened here. Your query is being interpreted as follows:
不要混合使用 ANSI-89 样式和 ANSI-92 样式连接。它们具有不同的优先级，这可能会导致混淆错误，这就是这里发生的情况。您的查询被解释如下：
FROM property p, (
propertygeometry pg
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
...
)
In the above, the joins using the JOIN keyword are evaluated first before the comma-style join is even considered. At that point the table pisn't yet declared.
在上面，使用 JOIN 关键字的连接在考虑逗号样式连接之前首先被评估。此时表p尚未声明。
From the MySQL manual:
从MySQL 手册：
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause'may occur. Information about dealing with this problem is given later in this section.
但是，逗号运算符的优先级小于 INNER JOIN、CROSS JOIN、LEFT JOIN 等。如果在存在连接条件时将逗号连接与其他连接类型混合使用，则可能会出现“on 子句”中的“未知列”列“col_name”形式的错误。本节稍后将提供有关处理此问题的信息。
I'd recommend alwaysusing ANSI-92 style joins, i.e. using the JOIN keyword:
我建议始终使用 ANSI-92 样式连接，即使用 JOIN 关键字：
SELECT p.*,
IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
pm.MediaID,
date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
astext(pg.Geometry) AS Geometry
FROM property p
JOIN propertygeometry pg ON p.PropertyGeometryID = pg.id
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
GROUP BY p.id
Related:
有关的：
回答by Tom Mulkins
As stated before there is a precedence issue using joins via the comma operator where the LEFT JOIN will be executed and so references to table aliases won't exist at that time. Though you can implicitly tell MySQL to use a JOIN via that statement you may also tell MySQL to evaluate the comma joined tables first, then execute left join thusly:
如前所述，通过逗号运算符使用连接存在优先级问题，其中将执行 LEFT JOIN，因此此时不存在对表别名的引用。虽然您可以通过该语句隐式告诉 MySQL 使用 JOIN，但您也可以告诉 MySQL 首先评估逗号连接的表，然后执行左连接：
SELECT p.*,
IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
pm.MediaID,
date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
astext(pg.Geometry) AS Geometry
FROM (property p, propertygeometry pg)
JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
AND p.PropertyGeometryID = pg.id
GROUP BY p.id
Notice the comma separated tables are contained within parenthesis (). The table aliases and columns will now be available to your other JOINs.
请注意逗号分隔的表格包含在括号 () 中。表别名和列现在可用于您的其他 JOIN。
回答by user742102
I bumped into this error unknown column, the diff is the query is built thru HQL inside session.executeQuery("select id, name, sum(paid), custType from cust group by brand") that's why having to manually type inner join or join keyword is not an option as the hql is the one generating it. it produces a query sumthing like this:
我遇到了这个错误未知列，不同之处在于查询是通过 HQL 在 session.executeQuery("select id, name, sum(paid), custType from cust group by brand") 中构建的，这就是为什么必须手动输入内部连接或join 关键字不是一个选项，因为 hql 是生成它的一个。它产生一个这样的查询总和：
select cust_id, name, sum(paid), c.custTypeId
from customer c, custType ct
on c.custTypeId = ct.custTypeId
it says "unknown c.custTypeId" column when I am 101% sure it bears that column.
当我 101% 确定它带有该列时，它会显示“未知的 c.custTypeId”列。
My classes/relations:
我的课程/关系：
Customer {
Integer custId
CustomerType custType
}
CustomerType{
Integer custTypeId
string code
}
the problem lies in the comma in "from customer, custType" line. it should be with the word JOIN as the answer stated above. but since it is HQL and is being generated, I can't do that. What I did is modified by query and instead of typing select custType, I typed select custType.id, custType.code
问题在于“来自客户，custType”行中的逗号。它应该与 JOIN 一词一起作为上述答案。但由于它是 HQL 并且正在生成，我不能这样做。我所做的是通过查询修改，而不是键入 select custType，我键入select custType.id, custType.code
I know it's basic but for first timers like me, it was a struggle.
我知道这是基本的，但对于像我这样的初学者来说，这是一场斗争。
