--得到指定id的子id列表
create function f_getchildid1(@id int)
returns @re table(id int,pid int,name varchar(20),level int)
as
begin
declare @i int,@nid int
set @i = 1
set @nid = @id while(exists(select 1 from tb a where a.pid!=0 and id=@nid))
begin
select @nid=a.pid,@i=@i+1 from tb a where id=@nid
end insert into @re select id,pid,name,@i from tb where id=@id
while @@rowcount>0
begin
set @i = @i+1
insert into @re select a.id,a.pid,a.name,@i
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
end return
end----执行函数
select * from f_getchildid1(1)
create function f_getchildid1(@id int)
returns @re table(id int,pid int,name varchar(20),level int)
as
begin
declare @i int,@nid int
set @i = 1
set @nid = @id while(exists(select 1 from tb a where a.pid!=0 and id=@nid))
begin
select @nid=a.pid,@i=@i+1 from tb a where id=@nid
end insert into @re select id,pid,name,@i from tb where id=@id
while @@rowcount>0
begin
set @i = @i+1
insert into @re select a.id,a.pid,a.name,@i
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
end return
end----执行函数
select * from f_getchildid1(1)
create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered
,pid int,name Nvarchar(20))
GO
--插入数据
insert into tb
select 0,N'中国'
union all select 0,N'美国'
union all select 0,N'加拿大'
union all select 1,N'北京'
union all select 1,N'上海'
union all select 1,N'江苏'
union all select 6,N'苏州'
union all select 7,N'常熟'
union all select 6,N'南京'
union all select 6,N'无锡'
union all select 2,N'纽约'
union all select 2,N'旧金山'
GO
--得到指定id的子id列表
create function f_getchildid(@id int)
returns @re table(id int,pid int,name Nvarchar(20),Level Int)
as
begin
Declare @Int Int
Set @Int=2
insert into @re select id,pid,name,2 from tb where pid=@id
insert into @re select id,pid,name,1 from tb where id=@id
while @@rowcount>0
Begin
Set @Int=@Int+1
insert into @re select a.id,a.pid,a.name,@Int
from tb a inner join @re b on a.pid=b.id
where a.id not in(select id from @re) end
return
end
GO
--测试
select * from f_getchildid(1) Order By level,id
--删除测试环境
Drop Function f_getchildid
Drop table tb
--结果
/*
id pid name level
1 0 中国 1
4 1 北京 2
5 1 上海 2
6 1 江苏 2
7 6 苏州 3
9 6 南京 3
10 6 无锡 3
*/
declare @tex table([id] int, pid int, [name] varchar(20), [level] int)
declare @texTmp table([id] int, pid int, [name] varchar(20), [level] int)
declare @Temp int
declare @Level int
declare @count int
set @temp = 1
set @Level = 1insert @tex
select *,@Level from tb
where pid = 0insert @table
select * from @texwhile @temp <> 0
begin
set @Level = @Level + 1
insert @texTmp
select *,@Level from tb
where pid in (select [id] from @tex) insert @table
select * from @texTmp delete @tex
insert @tex
select * from @texTmp select @temp = count(*) from @tex delete @texTmp
end
select * from @table