一个函数
一个表三个字段id,fid,name
create   table   t1   (iID   int   primary   key   identity(1,1),iFID   int   ,vName   varchar(20))

insert   into   t1(iFID,vName)
select   null, 'A '
union  
select   null, 'B '
union  
select   null, 'C '
union
select   1, 'A1 '
union  
select   2, 'B1 '
union  
select   1, 'A2 '
union
select   4, 'A1-a '
比如传入一个1如何返回A2和A1-a这两个值


------解决方案--------------------
再修正个细节

alter function fn_nod(
@id int
)
returns @r table (
iID int,iFID int ,vName varchar(20),lev int) --返回的字段可以在这里定义
as
begin
--先查询所有子节点
declare @lev int
set @lev=1
insert @r select iid,ifid,vname,@lev from t1 where ifid=@id
while exists (select 1 from t1 where ifid in (select iid from @r where lev=@lev))
begin
insert @r select iid,ifid,vname,@lev+1 from t1 where ifid in (select iid from @r where lev=@lev)
set @lev=@lev+1
end
--再删除非叶子阶段
delete a
from @r a
where exists (
select 1 from @r where ifid=a.iid
)
return
end
go

--调用
select * from dbo.fn_nod(1)

--结果
iID iFID vName lev
----------- ----------- -------------------- -----------
5 1 A2 1
7 4 A1-a 2

（所影响的行数为 3 行）