--建立测试表
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
end