MySQL 5查询报错Unknown table 'finding' in field list 求解决方案

问题说明 #

需要从finding表获取数据，其中Material字段关联采用父子结构的material表，核心需求是遍历每个finding.Material对应的所有父节点，检查是否包含值'Metal'，符合条件则在finding.MaterialGroup字段返回'Met'，否则返回空。

原查询语句执行时报错：Unknown table 'finding' in field list，原因是最内层初始化变量的子查询SELECT @r := finding.Material无法识别外层的finding表，换成硬编码值（如@r :=3）则能正常运行。原查询如下：

SELECT finding.ID, finding.Material,
(SELECT CASE
            WHEN 'Metal' IN (
                SELECT m2.Value FROM (SELECT @r AS _id, (SELECT @r := ParentID FROM material WHERE ID = _id) AS ParentID, @l := @l + 1 AS lvl 
                FROM (SELECT @r := finding.Material, @l := 0) vars, material m WHERE @r <> 0) m1 JOIN material m2 ON m1._id = m2.ID) 
            THEN 'Met' 
            ELSE '' 
        END AS result) 
AS 'finding.MaterialGroup',finding.Description FROM finding;

解决方案 #

方案一：通过关联初始化变量传递字段值 #

利用CROSS JOIN初始化全局变量，同时在子查询中引用外层finding表的字段，替换IN为EXISTS提升查询效率：

SELECT 
    f.ID,
    f.Material,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM (
                SELECT 
                    @r AS _id,
                    (SELECT @r := ParentID FROM material WHERE ID = _id) AS ParentID,
                    @l := @l + 1 AS lvl
                FROM (SELECT @r := f.Material, @l := 0) vars, material m
                WHERE @r <> 0
            ) m1 
            JOIN material m2 ON m1._id = m2.ID
            WHERE m2.Value = 'Metal'
        ) THEN 'Met'
        ELSE ''
    END AS `finding.MaterialGroup`,
    f.Description
FROM finding f
CROSS JOIN (SELECT @r := 0, @l := 0) init_vars;

方案二：创建自定义函数递归检查父节点 #

针对父子结构的遍历需求，创建自定义函数封装逻辑，让主查询更简洁：

DELIMITER //
CREATE FUNCTION HasMetal(material_id INT) RETURNS VARCHAR(3)
BEGIN
    DECLARE current_id INT DEFAULT material_id;
    DECLARE current_value VARCHAR(50);
    WHILE current_id <> 0 DO
        SELECT Value, ParentID INTO current_value, current_id FROM material WHERE ID = current_id;
        IF current_value = 'Metal' THEN
            RETURN 'Met';
        END IF;
    END WHILE;
    RETURN '';
END //
DELIMITER ;

调用函数的查询语句：

SELECT 
    ID,
    Material,
    HasMetal(Material) AS `finding.MaterialGroup`,
    Description
FROM finding;

测试用表结构与数据 #

以下是用于验证的建表和插入数据语句：

CREATE TABLE IF NOT EXISTS `finding` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Description` text,
  `Material` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=UTF8;

INSERT INTO finding
(id, `description`, material)
VALUES
(1, 'Test1', 1),
(2, 'Test2', 2),
(3, 'Test3', 4);

CREATE TABLE IF NOT EXISTS `material` (
  `ID` int(10) NOT NULL,
  `Value` varchar(50) NOT NULL DEFAULT '',
  `ParentID` int(10) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

INSERT INTO material
(id, `value`, ParentID)
VALUES
(1, 'Metal', 0),
(2, 'Tree', 1),
(3, 'Apple', 2),
(4, 'Table', 0);

预期输出：Test1、Test2的MaterialGroup字段值为'Met'，Test3为空。

内容的提问来源于stack exchange，提问作者juran.maurice