如何在SQL SERVER 2005中让父母给孩子 [英] How to get the parent given a child in SQL SERVER 2005
本文介绍了如何在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
如果我给孩子提供5,则父母将为1(输出)
If I give a childid as 5, the parentid will be 1(output)
如果我给孩子提供9,则父母将为7.(输出)
If I give a childid as 9, the parentid will be 7.(output)
即根父母代号为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
与众不同! :-)
这篇关于如何在SQL SERVER 2005中让父母给孩子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文