MYSQL查询以返回特定父级的所有后代

Question

我有这个父表。我的目标是找出给定id的所有后代。 例如，对于以下表格：

+----------+-----+
| parentId |  id |
+----------+-----+
|  0       |   1 |
|  0       |   2 |
|  0       |   3 |
|  0       |   4 |
|  1       |   5 |
|  1       |  11 |
|  5       |  12 |
| 12       |  13 |
| 14       |  15 |
| 19       |  20 |
| 20       |  24 |
+----------+-----+

给定父级为0，我想要得到：

+----+
| Id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
| 11 |
| 12 |
| 13 |
+----+

我的限制/注意事项： 1. 最坏情况下我可以有4个层次结构。 2. 我的数据库是MYSQL（这意味着我无法编写递归查询）。 3. 表格id_to_id相当小..最多只有100行。

我考虑的解决方案类似于以下SQL查询：

SELECT DISTINCT(T.Id)
FROM(
SELECT t1.Id
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0

UNION ALL

SELECT t2.Id as lev2
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0

UNION ALL

SELECT t3.Id as lev3
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0

UNION ALL

SELECT t4.Id as lev4
FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0) as T

WHERE T.Id IS NOT NULL;

但是那个内部查询会被执行4次（我理解错了吗？）：

FROM id_to_id AS t1
LEFT JOIN id_to_id AS t2 ON t2.parentId = t1.Id
LEFT JOIN id_to_id AS t3 ON t3.parentId = t2.Id
LEFT JOIN id_to_id AS t4 ON t4.parentId = t3.Id
WHERE t1.parentId = 0) as T

所以我的问题是：

• 有没有想法可以让它在不连接4次的情况下工作？或者对于该查询的另一种聪明解决方案？
• 如何编写查询，可以在表上执行相同的操作（我可以这样做吗？）而无需给定参数 - 类似于：

+----+------------+
| Id |  decedents |
+----+------------+
|  0 | 1,2,3,4,...|
|  1 | 5,11,...   |
+----+------------+

谢谢，Ido

- user1567004

---

这篇文章似乎适合你： https://dev59.com/02gv5IYBdhLWcg3wYvuh干得好！ ;) - Zio Panzu

你是否坚持要仅使用 SQL 来实现这个？我曾经遇到过一个无限层级的项目问题，在最终用于前端的编程语言中用过流程代码，感觉非常简洁。如果有兴趣，我可以发布那个解决方案。 - ATP_JD

Answer 1

使用MySQL中的表格没有更简单的方法来完成这个任务。在Oracle中，您可以使用CONNECT BY ... START WITH子句执行查询。

但是在MySQL中，您要么有一个映射表格，其中包含每个父级组合（例如，对于id 11，您将有两个条目：[0，11]和[1，11]。每个层次结构级别都有一个）。但这可能会破坏表格的目的。

另一个选择是像您已经做的那样进行4次连接。

第三种可能性是编写一个递归循环遍历表格并输出结果的过程。这也非常灵活，因为它不管您的层次结构有多深。

create function f_recursive_id_to_id_lookup(in parent_id int)
returns varchar(255)
begin
  -- declare a variable that we use later to break the while loop
  declare l_done tinyint default 0;
  -- the result variable
  declare l_result varchar(255) default '';

  -- table to hold parent ids and insert first id from parameter
  drop table if exists tmp_ids;
  create temporary table tmp_ids(id int, unique key (id));
  insert into tmp_ids(id)
  values (parent_id);

  -- table to hold result
  drop table if exists tmp_result;
  create temporary table tmp_result(id int, unique key (id));

  while l_done = 0 do
    -- insert new ids from previous loop (or none)
    insert ignore into tmp_ids(id)
    select id from tmp_result;

    -- insert new children
    insert ignore into tmp_result(id)
    select id
    from id_to_id i2i
      join tmp_ids tid
        on i2i.parentId = tid.id;

    -- if no rows were inserted, break the loop
    -- rows already present in the table are not counted
    if row_count() = 0 then
      set l_done = 1;
    end if;
  end while;

  -- select the result from the temp table to output to console
  select ifnull(group_concat(id),'') into l_result from tmp_results;
  return l_result;
end

你现在可以在查询中使用这个函数：

select parentId, f_recursive_id_to_id_lookup(parentId)
from id_to_id;

这将给您期望的结果。但是它可能会很慢，因为它将为100个parentIds进行100次递归查找。另外没有涵盖的是只有顶级ids被列出。这将列出所有ids。

- drunken_monkey