mysql - 级联选择直到在表中找到所有父级

Question

这个问题已经有答案了:

How to create a MySQL hierarchical recursive query? (16 个回答)

已关闭 7 年前。

在我的MySQL数据库中，我有一个名为units的表，其结构如下:

ID, Name, parentUnitID, UnitTypeID, ...

是否有可能以一个查询和一个ID为起点生成一条完整的单元链？只要给定 ID 有更多父级，就级联查询吗？我找不到在没有深度限制的情况下生成此内容的方法。

给定的 ID 可能有一个父级，而该 ID 本身也有一个父级，而该 ID 也可能有一个父级，...等等。

编辑:

我期待的结果是这样的:

IDDepth1, IDDepth2, IDDepth3, ..., NameDepth1, NameDepth2, NameDepth3, ...

或者

UnitTypeID1, Name1,  
UnitTypeID2, Name2,  
UnitTypeID3, Name3,  
...

获取给定 ID 的完整单元链并在 PHP 中解析它以构建级联数组。

编辑2:

我尝试过这样的事情

SELECT ID, Name
FROM Units as u1
WHERE UnitTypeID = "4"
AND EXISTS (
    SELECT ID, Name
    FROM Units as u2
    WHERE UnitTypeID = "5"
    AND u2.ParentUnitID = u1.ID
    AND EXISTS (
        SELECT ID
        FROM Units as u3
        WHERE ID = "1692820"
        AND u3.UnitTypeID = "6"
        AND u3.ParentUnitID = u2.ID
   )
);

但首先它是静态的，其次 EXISTS 的结果不是 SELECT 本身的一部分。

Answer 1

您可以通过编写一个存储过程来实现此目的，该存储过程会将父项 ID 插入临时表并循环遍历父项等。

我编写了以下过程作为一个快速简单的示例；这当然并不完美，但可以是一个开始

DELIMITER $$

drop procedure if exists get_unitschain$$

CREATE PROCEDURE get_unitschain (IN childUnitID INTEGER)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_chain; -- Will contain the parents ID's
    DROP TEMPORARY TABLE IF EXISTS tmp_processed; -- Copy of tmp_chain
    DROP TEMPORARY TABLE IF EXISTS tmp_parents; -- New parents identified by each WHILE loop

    CREATE TEMPORARY TABLE tmp_chain (
        ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
        UnitID INTEGER NOT NULL
    );

    CREATE TEMPORARY TABLE tmp_processed (
        UnitID INTEGER NOT NULL
    );

    CREATE TEMPORARY TABLE tmp_parents (
        UnitID INTEGER NOT NULL
    );

    INSERT INTO tmp_chain (UnitID) VALUES (childUnitId);
    INSERT INTO tmp_parents (UnitID) VALUES (childUnitId); -- To start the WHILE loop

    WHILE (SELECT COUNT(*) FROM tmp_parents) DO
        DELETE FROM tmp_parents;

        INSERT INTO tmp_parents (UnitID)
            SELECT units.parentUnitID 
                FROM tmp_chain
                    INNER JOIN units ON units.ID = tmp_chain.UnitId
                WHERE units.parentUnitID NOT IN (SELECT tmp_processed.UnitId FROM tmp_processed);

        INSERT INTO tmp_chain (UnitID) SELECT tmp_parents.UnitID FROM tmp_parents;
        INSERT INTO tmp_processed (UnitID) SELECT tmp_parents.UnitID FROM tmp_parents;
    END WHILE;

    SELECT units.UnitTypeID, units.Name FROM tmp_chain INNER JOIN units ON units.ID = tmp_chain.UnitID;

    DROP TEMPORARY TABLE tmp_chain;
    DROP TEMPORARY TABLE tmp_processed;
    DROP TEMPORARY TABLE tmp_parents;
END$$

使用程序:

CALL get_unitschain([child id]);