如何找到根节点(How to find root-nodes)

id  Parant_ID       sort_nm    Scheme_Name
5   5               CAMPA      CAMPA
6   5               NPV        Net Present Value
7   5               CA         Compensatory Afforestation
8   6               ACA        Additional Compensatory  Afforestation
43  8               asd        asdasd
45  45              new        new
46  45              asdaasdas  asdasdasdas

我在SQL Server中上述树形结构。
我想知道每个节点的根节点ID。

Answer 1:

您可以使用递归CTE 。 从根开始，并通过递归携带RootID。

with C as
(
  select id,
         Parant_ID,
         sort_nm,
         Scheme_Name,
         id as RootID
  from YourTable
  where id = Parant_ID
  union all
  select T.id,
         T.Parant_ID,
         T.sort_nm,
         T.Scheme_Name,
         C.RootID
  from YourTable as T
    inner join C
      on T.Parant_ID = C.id
  where T.id <> T.Parant_ID
)
select *
from C

SE-日期

文章来源: How to find root-nodes