为孩子获取所有父母
我想检索一个 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
点击这里查看结果
相关文章