如何在SQL SERVER 2005中根据子项获取父项

Question

我有一个像这样的表格：

childid      parentid
------------------------
1       0
2       1
3       2
4       2
5       3
6       4
7       0
8       7
9       8
10      1

如果我给出一个childid为5，那么parentid就是1（输出）。

如果我给出一个childid为9，那么parentid就是7（输出）。

也就是说，根节点的parentid为0，查询应该在那里停止。

如何解决这样的查询？

请帮忙。

- priyanka.sarkar

---

你提供的数据中没有childid为5或9吗？ - Spencer Ruport

我不理解你的例子，它们与数据不匹配。 - D'Arcy Rittich

你的问题没有意义。请再读一遍。 - Saif Khan

也许列的标题被倒置了。 - Jonathan

列名不太好，他想提供ParentID为5，并递归向上查找链，直到找到一个ChildID为0的链，即ParentID = 1。 - AndyMcKenna

AndyMcKenna已经成功理解了这个糟糕的问题，而我们没有 :) - Andrei Rînea

Answer 1

我认为你应该将child_id重命名为node，parent_id重命名为child_of。你的列命名有点混乱。

create table stack_overflow
(
node int, child_of int
);


insert into stack_overflow(node, child_of) values
(1,0),
(2,1),
(3,2),
(4,2),
(5,3),
(6,4),
(7,0),
(8,7),
(9,8),
(10,1);

这适用于任何支持CTE的关系型数据库管理系统：

with find_parent(parent, child_of, recentness) as
(
    select node, child_of, 0 
    from stack_overflow
    where node = 9
    union all
    select i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select top 1 parent from find_parent 
order by recentness desc

输出：

parent
7

[编辑：更灵活和未来可持续]:

with find_parent(node_group, parent, child_of, recentness) as
(
    select node, node, child_of, 0
    from stack_overflow
    where node in (5,9)
    union all
    select fp.node_group, i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select q.node_group as to_find, parent as found 
from find_parent q 
join
(
    select node_group, max(recentness) as answer
    from find_parent
    group by node_group 
) as ans on q.node_group = ans.node_group and q.recentness = ans.answer 
order by to_find    

输出：

to_find     found
5           1
9           7

如果您正在使用 Postgres，上述代码可以简化为：

with recursive find_parent(node_group, parent, child_of, recentness) as
(
    select node, node, child_of, 0
    from stack_overflow
    where node in (5,9)
    union all
    select fp.node_group, i.node, i.child_of, fp.recentness + 1
    from stack_overflow i
    join find_parent fp on i.node = fp.child_of
)
select distinct on (node_group) node_group as to_find, parent as found 
from find_parent 
order by to_find, recentness desc

在 rocks 字段上去重！:-)

- Michael Buen

Answer 2

如果你只想要根ParentID，你可以使用这个递归函数：

CREATE FUNCTION test_func
(
    @ParentID int
)
RETURNS int
AS
BEGIN
    DECLARE @result int;
    DECLARE @childID int;

    SET @childID = (SELECT ChildID FROM YourTable WHERE ParentID = @ParentID)

    IF (@childID = 0)
        SET @result = @ParentID
    ELSE
        SET @result = dbo.test_func(@childID)

    RETURN @result    
END
GO

然后在您的主查询中：

SELECT dbo.test_func(5)

根据提供的数据，传入5返回1，传入9返回7。如果您需要沿着该链路的每个ParentID，则应该使用CTE。

- AndyMcKenna

---

我遇到了错误Msg 217，级别 16，状态 1，行 12 超过最大存储过程、函数、触发器或视图嵌套级别（限制为 32）。 - priyanka.sarkar

它适用于距离根节点不超过32级的子节点吗？ - AndyMcKenna

Answer 3

一个简单的示例，获取与给定子ID匹配的父ID是：

select parentid 
from MyTable 
where childid = 5

然而，对于上述数据，这将不会返回任何记录。

- D'Arcy Rittich

Answer 4

我认为你需要一个递归查询，你应该使用公共表达式（CTE）。我会给你一个链接，里面有一个非常类似于你正在使用的示例。

我认为这里就是解决方案。几个月前，它帮助过我。

- Jonathan