MySQL字段列表中的未知表

af7jpaap  于 2023-05-16  发布在  Mysql
关注(0)|答案(1)|浏览(130)

我有一个特定的mysql(版本5)查询:

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;

查询从“查找”表中搜索数据。该表包含“材料”列。此列引用另一个表,该表是根据子-父原则构造的。
这个复杂的子查询的目的是列出Material列的所有父项,以便检查其中的特定值。
问题:我得到错误“Unknown table 'finding' in field list”。所以我不能使用代码:“@r:=查找.Material”。在子查询中,可能无法识别查找表,因为它不在子查询的范围内。
如果我试着遵循代码,它就能工作。

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 := 3, @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;

现在有人有解决办法吗?
下面是设置示例表的代码,如果你想自己尝试的话:

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);

我希望结果是这样的:

idfiyjo8

idfiyjo81#

在MySQL 5中,递归子查询是不可用的,所以我的想法是写递归函数。但它们也不可用。但是你可以写一个函数调用递归过程,所以:
查询:

SELECT ID, Material, case when find_metal(material) = 1 then 'Met' END AS result 
FROM finding;

功能:

CREATE FUNCTION find_metal(i_id int) RETURNS int
  
BEGIN
    DECLARE res int;
    CALL get_metal(i_id, res);
    RETURN res;
END;

函数调用的递归过程:

create procedure get_metal (i_id int, out ret int)
begin
  
  declare v_value varchar(50);
  declare v_parent_id int;
  set max_sp_recursion_depth=50;

  select value, parentid into v_value, v_parent_id from material where id = i_id;

  if (v_value = 'Metal') then 
    set ret = 1;
  elseif v_parent_id = 0 then 
     set ret = 0;
  else 
    call get_metal(v_parent_id, ret);
  end if;
end;

dbfiddle demo

相关问题