如何找到树表示的层次结构路径 [英] How to find the hierarchy path for a tree representation

问题描述

我有一个树形层次结构，它看起来是内置在表中的，并且parent_id指向前一个根节点.

I have a tree hierarchy look this built into a table with the parent_id pointing to the previous root node.

我正在遍历所有根节点(root1，root2)，并且将root1和child1的路径设置为root1或root1/child1.为了找到child1的路径，我将至少要调用2才能形成路径.有没有一种有效的方法来填充路径，因为我们处理了嵌套在5-7层深处的大量根节点和子节点.

I am iterating through all root nodes (root1, root2) and I am setting path to either root1 or root1/child1 for root1 and child1. In order to find the path for child1, I will have to make at-least 2 calls to form the path. Is there an efficient way to fill the path, since we deal with a very large number of root nodes and children which are nested 5-7 levels deep.

create table foo (id, name, parent_id, path)
insert into foo (1, "root1', null, null)
insert into foo (2, "child1', 1, null)

root1 (path = null)
  child1 (path = root1)
    subchild1 (path = root1/child1)

root2
   child2
     subchild2

推荐答案

您可以使用问题中提到的存储过程，因为嵌套最多可以达到7层.

You can go with a stored procedure as you have mentioned in your question as the nesting can be up to 7 level deep.

CREATE PROCEDURE updatePath()
BEGIN
declare cnt, n int;
    select count(*) into n from foo where parent_id is null;
    update foo a, foo b set a.path = b.name where b.parent_id is null and a.parent_id = b.id;
    select count(*) into cnt from foo where path is null;
    while cnt > n do
        update foo a, foo b set a.path = concat(b.path, '/', b.name) where b.path is not null and a.parent_id = b.id;
        select count(*) into cnt from foo where path is null;
    end while;
END//

要检查实际记录，我们只在路径列中打印了具有空值的纯记录

To check the actual record we just printed the plain records having null value in path column

select * from foo

结果:

| ID |         NAME | PARENT_ID |   PATH |
------------------------------------------
|  1 |        root1 |    (null) | (null) |
|  2 |       child1 |         1 | (null) |
|  3 |    subchild1 |         2 | (null) |
|  4 |       child2 |         1 | (null) |
|  5 |       child3 |         1 | (null) |
|  6 |    subchild2 |         4 | (null) |
|  7 | subsubchild1 |         6 | (null) |

调用过程:

call updatepath

过程执行后的结果:

select * from foo

结果:

| ID |         NAME | PARENT_ID |                   PATH |
----------------------------------------------------------
|  1 |        root1 |    (null) |                 (null) |
|  2 |       child1 |         1 |                  root1 |
|  3 |    subchild1 |         2 |           root1/child1 |
|  4 |       child2 |         1 |                  root1 |
|  5 |       child3 |         1 |                  root1 |
|  6 |    subchild2 |         4 |           root1/child2 |
|  7 | subsubchild1 |         6 | root1/child2/subchild2 |

SQLFIDDLE

希望这会有所帮助....

SQLFIDDLE

Hope this helps....

这篇关于如何找到树表示的层次结构路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！