为孩子获取所有父母 [英] Get all parents for a child

问题描述

我想检索一个 id 的 parentid，如果该 parentid 有一个 parent，则再次检索它，依此类推.一种层次结构表.

I want to retrieve the parentid of an id, if that parentid has a parent again retrieve it, and so on. Kind of hierarchy table.

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

我是 sql server 的新手并尝试过一些查询，例如:

am new to sql server and tried, some queries like:

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;

它只给我一行.而且我想将这些记录插入到临时表变量中.我怎样才能做到这一点.提前致谢.抱歉问了一个简单的问题(虽然不是对我)

It is giving me only one row. and also I want to insert these records into a temporary table variable. How can I do this. thanks in advance. sorry for asking the simple question(though not to me)

推荐答案

试试这个让孩子的所有父母都知道

Try this to get all parents of a child

;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

点击这里查看结果

如果你想插入一个表变量，你可以这样做:

If you want to insert into a table variable, you can do something like:

-- 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

点击这里查看结果

这篇关于为孩子获取所有父母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！