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若需判断‘BB’是不是'CC'的子树,只需: if exists( select * from dbo.bom('bb') where parent = (select levelid from 表 where levelName = 'CC') ) print '是' else print '不是'
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若需判断‘BB’是不是'CC'的子树,只需:
if exists(
select * from dbo.bom('bb') where parent = (select levelid from 表 where levelName = 'CC')
)
print '是'
else
print '不是'
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.454632