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 执行相同的查询(我可以这样做吗？) - 类似于:

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

谢谢，伊多

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 个 parentId 进行 100 次递归查找。还没有涵盖的是只列出了顶级 ID。这会列出所有 ID。