--建立测试表
create table info (
ID int ,
UPID int
)
go--插入数据
insert info
select
1,null
union all select
2, 1
union all select
3, 1
union all select
4, 2
union all select
5, 1
union all select
6, 3
union all select
7, 2 go--建立函数
CREATE function fn_Num(@Id int)
returns int
as
begin
declare @r int
set @r=0
if not exists (select 1 from info where Id=@id)
return @r
if not exists (select 1 from info where UpId=@id)
return @r
declare @t table (Id int) insert @t(Id) select id from info where Upid=@id while exists (select Id from info where UpId in (select Id from @t)
and Id not in (select Id from @t)
)
insert @t(Id)
select Id from info where UpId in (select Id from @t)
and Id not in (select Id from @t)
select @r=count(distinct Id) from @t
return @r
endGO--调用测试
select dbo.fn_Num(1)--结果
/*
-----------
6(所影响的行数为 1 行)*/
create table info (
ID int ,
UPID int
)
go--插入数据
insert info
select
1,null
union all select
2, 1
union all select
3, 1
union all select
4, 2
union all select
5, 1
union all select
6, 3
union all select
7, 2 go--建立函数
CREATE function fn_Num(@Id int)
returns int
as
begin
declare @r int
set @r=0
if not exists (select 1 from info where Id=@id)
return @r
if not exists (select 1 from info where UpId=@id)
return @r
declare @t table (Id int) insert @t(Id) select id from info where Upid=@id while exists (select Id from info where UpId in (select Id from @t)
and Id not in (select Id from @t)
)
insert @t(Id)
select Id from info where UpId in (select Id from @t)
and Id not in (select Id from @t)
select @r=count(distinct Id) from @t
return @r
endGO--调用测试
select dbo.fn_Num(1)--结果
/*
-----------
6(所影响的行数为 1 行)*/
你的方法很好,但我用不上,因为我的这个树有可能有十几层,也就是说4下面可能有,7下面也可以有,一直延续到很多层,所以我用不上,不过还是谢谢你。
select *,dbo.fn_Num(id) as cnt
from info/*
结果:
ID UPID cnt
----------- ----------- -----------
1 NULL 6
2 1 2
3 1 1
4 2 0
5 1 0
6 3 0
7 2 0(所影响的行数为 7 行)*/