获取一个子节点的所有父节点

Question

我想检索出一个id的parentid，如果该parentid再次有父元素，请检索它，以此类推。这是一种层次结构表。

id----parentid
1-----1
5-----1
47894--5
47897--47894

我是 SQL Server 的新手，并尝试了一些查询，如：

with name_tree as 
(
   select id, parentid
   from Users
   where id = 47897 -- this is the starting point you want in your recursion
   union all
   select c.id, c.parentid
   from users c
   join name_tree p on p.id = c.parentid  -- this is the recursion
) 
select *
from name_tree;

它只给了我一行记录。我还想将这些记录插入到一个临时表变量中。我该怎么做。谢谢你提前。对于问这个简单问题（虽然不是对我来说）感到抱歉。

- srinioracle

Answer 1

尝试使用以下代码获取一个子元素的所有父元素：

;with name_tree as 
(
   select id, parentid
   from Users
   where id = 47897 -- this is the starting point you want in your recursion
   union all
   select C.id, C.parentid
   from Users c
   join name_tree p on C.id = P.parentid  -- this is the recursion
   -- Since your parent id is not NULL the recursion will happen continously.
   -- For that we apply the condition C.id<>C.parentid 
    AND C.id<>C.parentid 
) 
-- Here you can insert directly to a temp table without CREATE TABLE synthax
select *
INTO #TEMP
from name_tree
OPTION (MAXRECURSION 0)

SELECT * FROM #TEMP

点击这里查看结果

编辑:

如果你想插入到一个表变量里，可以像这样做：

-- Declare table varialbe
Declare @TABLEVAR table (id int ,parentid int)


;with name_tree as 
(
   select id, parentid
   from #Users
   where id = 47897 -- this is the starting point you want in your recursion
   union all
   select C.id, C.parentid
   from #Users c
   join name_tree p on C.id = P.parentid  -- this is the recursion
   -- Since your parent id is not NULL the recursion will happen continously.
   -- For that we apply the condition C.id<>C.parentid 
    AND C.id<>C.parentid 
) 
-- Here you can insert directly to table variable
INSERT INTO @TABLEVAR
select *
from name_tree
OPTION (MAXRECURSION 0)

SELECT * FROM @TABLEVAR

点击这里查看结果

- Sarath Subramanian

---

你要如何修改代码以获取一个父元素的所有子元素？ - David

Answer 2

你的查询正在进行相反方向递归。所以如果你把起始点改成：

where id = 1

那么你将拥有用户1及其所有的后继者

- rtruszk

Answer 3

你没有提到所需的输出和输入。 不过你可以尝试这样做，

Declare @t table (id int ,parentid int)
insert into @t
select 1,1 union all
select 5,1 union all
select 47894,5 union all
select 47897,47894 

;With CTE as
(
select * from @t where id=1
union all
Select a.* from @t a inner join cte b
 on b.id=a.parentid and
a.id<>b.id
)
select * from cte

- KumarHarsh