在sqlserver中查找父代的所有子代 [英] Finding all childern of a parentid in sqlserver

问题描述

朋友你好，我有这样的桌子.

ID父母
1 Null
2 1
3 2
4 Null
5 1
6 4

现在，我想在此表中将所有孩子的父母ID计数到n级.例如ID 1有3个孩子，4个有1个孩子，2个有1个，依此类推.请帮助我，我该如何实现呢?
在此先感谢朋友.

hello friends i have a table like this.

ID Parent
1 Null
2 1
3 2
4 Null
5 1
6 4

Now i want to count all children for a parent id in this table up to n level. like ID 1 have 3 children, 4 has 1 child , 2 has one and so on. Please help me , how can i achieve that .
thanks in advance friends.

推荐答案

尝试一下...

创建函数[dbo].[myfunction]
(
@p_P int
)
返回int
开始
声明@output int
从P_table中选择@ output = count(*)，其中parent = @ p_P
返回@output
结束
去
从p_table中选择不同的父项dbo.myfunction(parent)作为"No of child(ren)"

try this...

create function [dbo].[myfunction]
(
@p_P int
)
returns int
begin
declare @output int
select @output=count(*) from P_table where parent=@p_P
return @output
end
go
select distinct parent, dbo.myfunction(parent) as ''No of child(ren)'' from p_table

首先，查看此查询的输出:

Firstly, have a look at the output of this query :

declare @nodeid int
set @nodeid = 109;

with rootCTE(id, parentid, level )
as
(
select id, parent ,1
from table
    where parent = @nodeid

union all
select e.id, e.parent , r.level+1 from table t, rootCTE r where t.parent = r.id
)
select * from rootCTE

然后，要使儿童的数量达到2级，请运行此程序:

Then to get the count of children up to level 2 run this one :

declare @nodeid int
set @nodeid = 109;

with rootCTE(id, parentid, level )
as
(
select id, parent ,1
from table
    where parent = @nodeid

union all
select e.id, e.parent , r.level+1 from table t, rootCTE r where t.parent = r.id
)
select COUNT(*)  from rootCTE
where level<=2

要计算所有子项，请删除where子句.

To count all children remove the where clause.

这篇关于在sqlserver中查找父代的所有子代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！