eg:
create table 表(levelid int,levelname char(2),parent int)
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount > 0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
endselect * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2(所影响的行数为 2 行)
create table 表(levelid int,levelname char(2),parent int)
insert 表 select 1, 'AA' , 0
union all select 2 , 'BB' , 1
union all select 3 , 'CC' , 1
union all select 4 , 'DD' , 2
union all select 5 , 'EE' , 3
union all select 6 , 'FF', 5create function bom (@name char(2))
returns @tb table (levelid int,levelname char(2),parent int)
as
begin
insert @tb select levelID,LevelName,parent from 表 where Levelname = @name
while @@rowcount > 0
insert @tb select levelID,LevelName,parent from 表
where parent in (select levelID from @tb)
and levelID not in (select levelID from @tb)
return
endselect * from dbo.bom('bb')
levelid levelname parent
----------- --------- -----------
2 BB 1
4 DD 2(所影响的行数为 2 行)
create table 表(dirid int,dir nvarchar(10),dirparentid int)
insert into 表 select '1','教学资料','0'
insert into 表 select '2','内部资料','0'
insert into 表 select '3','商务资料','0'
insert into 表 select '4','编程资料','1'
insert into 表 select '5','图片资料','4'
insert into 表 select '7','临时目录','0'create function bom1 (@id int)
returns @tb table (dirid int,dir nvarchar(10),dirparentid int)
as
begin
insert @tb select dirid,dir,dirparentid from 表 where dirid = @id
while @@rowcount > 0
insert @tb select dirid,dir,dirparentid from 表
where dirparentid in (select dirid from @tb)
and dirid not in (select dirid from @tb)
return
endselect * from dbo.bom1(1)
dirid dir dirparentid
----------- ---------- -----------
1 教学资料 0
4 编程资料 1
5 图片资料 4(所影响的行数为 3 行)