获取一个递归的父级列表

Question

使用MySQL，我想要从一个表中返回一个父级列表，该表具有以下字段结构：ID，PARENTID，NAME（标准的父子层次结构）。我想要遍历“向上”树来返回所有“父级”的列表。

我意识到，“嵌套集合”可能是更好的处理方式 - 但目前我无法更改数据结构。我将来会考虑这样做。目前-我的数据集实际上只包含几个深度级别-不会很复杂...也许是2-5，因此我的递归查询不应该太昂贵。

我已经查看了在SQL Server get parent list中提出的解决方案 - 但是这个语法在mysql中失败了...

有没有人有如何做到这一点的示例？

@kevin - 感谢链接 - 但我仍然遇到错误。（“每个派生表都必须有自己的别名”）

这是我所做的事情（修改了上面文章的语法以'适应' MySQL） - 我显然错过了什么...

SELECT parents.*
FROM  (
    SELECT taskID,  task,  parentID,  0 as level
    FROM   tasks
    WHERE taskidID = 9147
    UNION ALL
    SELECT  taskID, task,  parentID,  Level + 1 
    FROM   tasks
    WHERE  taskID = (SELECT parentID FROM parents ORDER BY level DESC LIMIT 1)
    )

你有什么想法？

示例：

ID      PARENTID    NAME
9146    0       thing1
9147    0       thing2
9148    9146        thing3
9149    9148        thing4
9150    0       thing5
9151    9149        thing6

查询"thing3"的父级节点 返回结果为"9148,9146"

查询"thing6"的父级节点 返回结果为"9149,9148,9146,0"

- j-p

---

请参考类似的问题：https://dev59.com/_XM_5IYBdhLWcg3waiiJ - Kevin

@Kevin 我认为这与问题无关。 - Karolis

@karolis - 这有点相关，我明白他指引我的方向了..请参见上面的编辑... - j-p

@jpmyob 所以为该表添加一个别名，例如 as my_alias。但无论如何，不可能将 SQL Server 的查询重写为 MySQL，因为 MySQL 不支持递归子查询。如果您不想将查询限制在几个级别之内，则需要创建存储函数或过程。 - Karolis

Answer 1

我为您制作了一个小函数，我在我的数据库（MAMP）中进行了检查，它可以正常工作。

use mySchema;
drop procedure if exists getParents;

DELIMITER $$
CREATE PROCEDURE getParents (in_ID int)
BEGIN
DROP TEMPORARY TABLE IF EXISTS results;
DROP TEMPORARY TABLE IF EXISTS temp2;
DROP TEMPORARY TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 AS
  select distinct ID, parentID
    from tasks
    where parentID = in_ID;

create TEMPORARY table results AS
  Select ID, parentID from temp1;

WHILE (select count(*) from temp1) DO
  create TEMPORARY table temp2 as
    select distinct ID, parentID 
      from tasks 
      where parentID in (select ID from temp1);

  insert into results select ID, parentID from temp2;
  drop TEMPORARY table if exists temp1;
  create TEMPORARY table temp1 AS
    select ID, parentID from temp2;
  drop TEMPORARY table if exists temp2;

END WHILE;


select * from results;

DROP TEMPORARY TABLE IF EXISTS results;
DROP TEMPORARY TABLE IF EXISTS temp1;

END $$
DELIMITER ;

这段代码将返回所有祖先节点，无论有多少层级。 你可以显然地向结果中添加任何其他字段。

像这样使用它：

call getParents(9148)

例如

- UV.

---

你可以使用4个空格来突出显示代码，而不是使用<pre><code>。 - j0k

这个函数看起来更像是返回一个ID的子元素。 - htafoya

Answer 2

在这个例子中，我们将检查5级的层次结构：

select 
    t1.parentid, t2.parentid, t3.parentid, t4.parentid, t5.parentid
from
    tableName t1
    left join tableName t2 on t1.parentid = t2.id
    left join tableName t3 on t2.parentid = t3.id
    left join tableName t4 on t3.parentid = t4.id
    left join tableName t5 on t4.parentid = t5.id
where
    t1.name = 'thing3'

- Karolis

---

阅读问题中的编辑后，OP 正在尝试处理一个递归 SQL（由 UNION ALL 连接的两个 SELECT），而不是像你的固定五级树那样。 - Louis

@Louis，是的，但MySQL不支持递归查询。此外，OP说：_我的数据集实际上将包含几个深度级别_。因此，这可以是某种解决方案，因为它可以处理多达5个级别。另一种方法是编写存储函数/过程。 - Karolis

@louis - 不完全是这样，我提供了联合操作，因为它来自“其他帖子”，涉及父级字符串的问题...无论如何-它不起作用...这可能会起作用-我将不得不在上下文中尝试一下。谢谢。 - j-p