MySQL ON 子句中的未知列

Question

我有以下 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

我收到此错误：

#1054 - 'on Clause' 中的未知列 'p.id'

据我所知，查询看起来是正确的，任何想法可能出什么问题了？

Answer 1

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

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

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

来自 MySQL 手册：

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

我建议始终使用 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

相关：

• 为什么 SQL ANSI-92 标准没有比 ANSI-89 更好地采用？

Answer 2

如前所述，通过逗号运算符使用联接存在优先级问题，其中将执行 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

请注意，逗号分隔的表包含在括号 () 内。表别名和列现在可供其他 JOIN 使用。

Answer 3

我遇到了这个错误未知列，差异是查询是通过 session.executeQuery("select id, name, sum(paid), custType from cust group by Brand") 中的 HQL 构建的，这就是为什么必须手动键入内部联接或join 关键字不是一个选项，因为 hql 是生成它的选项。
它会生成这样的查询总和：

select cust_id, name, sum(paid), c.custTypeId
from customer c, custType ct
on c.custTypeId  = ct.custTypeId 

当我 101% 确定它包含该列时，它会显示“未知的 c.custTypeId”列。

我的类/关系：

Customer {
Integer custId
CustomerType custType
}

CustomerType{
 Integer custTypeId
string code
}

问题出在“from customer, custType”行中的逗号。应该与上述答案中的 JOIN 一词一起使用。但由于它是 HQL 并且正在生成，所以我不能这样做。我所做的是通过查询进行修改，我没有输入 select custType，而是输入了 select custType.id, custType.code

我知道这很基本，但对于像我这样的初学者来说，这是一场斗争。

Answer 4

以防万一有人像这样搬起石头砸自己的脚。

我是 MySQL Workbench 的新手，正在测试新模式，但我并没有想到在推送到服务器进行测试时选择添加 DROP 命令。

这意味着当我调整表格并更新视图时，我不断收到这些奇怪的错误。

长话短说，确保在早期测试阶段每次推送时都实际更新服务器上的表架构，否则您的新视图可能会因为服务器上的旧表而失败。

Answer 5

如果这对某人有帮助（以及对未来我自己的注释），我在尝试在 MariaDB 中执行以下查询时遇到此错误：

SELECT a.name, b.name
FROM `cities` as a
INNER JOIN `countries` as b 
ON `a.country_id` = `b`.`id`;

而我应该这样写：

SELECT a.name, b.name
FROM `cities` as a
INNER JOIN `countries` as b 
ON `a`.`country_id` = `b`.`id`;

我将把它留给读者来发现差异锻炼。 :)