ON 子句中的 MySQL 未知列

2021-11-20 00:00:00 sql mysql mysql-error-1054

我有以下 MySQL 查询:

I have the following MySQL query:

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

我收到此错误:

#1054 - on 子句"中的未知列p.id"

就我所见，查询看起来是正确的，不知道哪里出了问题?

As far as I can see the query looks right, any idea what could be wrong?

推荐答案

不要混合使用 ANSI-89 样式和 ANSI-92 样式的连接.它们具有不同的优先级，这可能会导致混淆错误，这就是这里发生的情况.您的查询被解释如下:

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:

FROM property p, (
    propertygeometry pg
    JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
    ...
)

在上面，使用 JOIN 关键字的连接在考虑逗号样式连接之前首先被评估.此时表 p 尚未声明.

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 p isn't yet declared.

来自 MySQL 手册:

但是，逗号运算符的优先级小于 INNER JOIN、CROSS JOIN、LEFT JOIN 等.如果在存在连接条件时将逗号连接与其他连接类型混合使用，则可能会出现 Unknown column 'col_name' in 'on clause' 形式的错误.本节稍后将提供有关处理此问题的信息.

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.

我建议总是使用 ANSI-92 样式的连接，即使用 JOIN 关键字:

I'd recommend always using ANSI-92 style joins, i.e. using the JOIN keyword:

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

相关:

  • 为什么 SQL 不是 ANSI-92 标准比 ANSI-89 更好采用吗?

相关文章