如何在 SQL SERVER 2005 中获得给定孩子的父母 [英] How to get the parent given a child in SQL SERVER 2005

问题描述

我有一张这样的桌子

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(输出)

If I give a childid as 5, the parentid will be 1(output)

如果我给 childid 为 9，那么 parentid 将为 7.(output)

If I give a childid as 9, the parentid will be 7.(output)

即根 parentid 为 0，查询应停止.

i.e. the root parentid is 0 and the query should stop there.

如何解决这样的查询?

请帮忙.

推荐答案

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

I think you should rename your child_id to node, your parent_id to child_of. Your column naming is a bit confusing

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 的 RDBMS:

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，上面的代码可以缩短为:

If you're using Postgres, the above code could be shortened to:

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

在岩石上与众不同！:-)

DISTINCT ON rocks! :-)

这篇关于如何在 SQL SERVER 2005 中获得给定孩子的父母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！