让所有父母为孩子 [英] Get all parents for a child
本文介绍了让所有父母为孩子的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想检索一个id的父母ID,如果该父母ID有一个父母再次检索它,依此类推. 层次表的种类.
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屋!
查看全文