SQL:基于主键查询完整的层次结构

Question

我们有一个类似下面的表格

folderid    name    parent
==========================
1           one     null
2           two     1
3           three   2
4           four    3
5           five    4
6           six     5

有没有办法在给定folderid的情况下检索完整的记录列表。例如，如果传递1，它应该返回直到叶为6的完整层次结构。如果传递6，它应该返回直到根为1的完整层次结构。如果传递4，它应该返回从根到叶的完整层次结构，从1到6。

Answer 1

您可以使用递归CTE：

with cte as (
      select folderid
      from t
      where folderid = 1
      union all
      select t.folderid
      from cte join
           t
           on cte.folderid = t.parent
     )
select *
from cte
option (maxrecursion 0);

如果需要更多列，可以将它们包含在递归CTE中，也可以在外部查询中联接它们。

Here是一个db<>fiddle。

编辑：

如果你想在树上来回走动，我推荐两个CTE：

with cte_c as (
      select folderid, 1 as lev
      from t
      where folderid = 4
      union all
      select t.folderid, lev + 1
      from cte_c join
           t
           on cte_c.folderid = t.parent
     ),
     cte_p as (
      select parent, 1 as lev
      from t
      where folderid = 4
      union all
      select t.parent as folderid, lev + 1
      from cte_p join
           t
           on cte_p.parent = t.folderid
      where t.parent is not null
     )
select folderid
from cte_c
union all
select parent
from cte_p
where parent is not null
option (maxrecursion 0);

Here是此版本的db<>fiddle。