如何返回前后各2级节点

Question

/*
a1 ------------一级节点
 a11-----------二级节点
  a111---------三级节点
  a112---------三级节点
   a1121-------四级节点
   a1122-------四级节点
    a11221-----五级节点
     a112211---六级节点
      a1122111-七级节点
     a112212---六级节点
      a1122121-七级节点
    a11222-----五级节点
    a11223-----五级节点
   a1123-------四级节点
 a12-----------二级节点
  a121---------三级节点
  a122---------三级节点
 a13-----------二级节点
*/
create table Test(
id int ,
pid int,
        xm varchar(20)
)
insert into test select 1,0,'a1'
union all select 2,0,'b1'
union all select 3,0,'c1'
union all select 4,1,'a11'
union all select 5,1,'a12'
union all select 6,2,'b11'
union all select 7,4,'a111'
union all select 8,4,'a112'
union all select 9,8,'a1121'
union all select 10,8,'a1122'
union all select 11,8,'a1123'
union all select 12,10,'a11221'
union all select 13,10,'a11222'
union all select 14,10,'a11223'
union all select 15,12,'a112211'
union all select 16,12,'a112212'
union all select 17,15,'a1122111'
union all select 17,16,'a1122121'返回一个节点的直系前后各2级节点，如节点为a1122，则返回
 a11-----------二级节点
  a111---------三级节点
  a112---------三级节点
   a1122-------四级节点*
    a11221-----五级节点
     a112211---六级节点
     a112212---六级节点
    a11222-----五级节点

Answer 1

返回一个节点的直系前后各2级节点，如节点为a1122，则返回 
a11-----------二级节点 
  a111---------三级节点 
  a112---------三级节点 
  a1122-------四级节点* 
    a11221-----五级节点 
    a112211---六级节点 
    a112212---六级节点 
    a11222-----五级节点
-----------------------
a1122的后2级节点中为什么不包括a11222,a11223等等?

Answer 2

create table Test( 
id int , 
pid int, 
        xm varchar(20) 
) 
insert into test select 1,0,'a1' 
union all select 2,0,'b1' 
union all select 3,0,'c1' 
union all select 4,1,'a11' 
union all select 5,1,'a12' 
union all select 6,2,'b11' 
union all select 7,4,'a111' 
union all select 8,4,'a112' 
union all select 9,8,'a1121' 
union all select 10,8,'a1122' 
union all select 11,8,'a1123' 
union all select 12,10,'a11221' 
union all select 13,10,'a11222' 
union all select 14,10,'a11223' 
union all select 15,12,'a112211' 
union all select 16,12,'a112212' 
union all select 17,15,'a1122111' 
union all select 17,16,'a1122121' 
create function f_id(@xm varchar(20)) --找子节点函数
returns @t table(Id int,pid int,xm varchar(20),lev int)
as
begin
insert into @t select id,pid,xm,1 from test
where xm=@xmdeclare @i int 
set @i=1while (@@rowcount>0 and @i<3)
begin
set @i=@i+1
insert into @t select a.id,a.pid,a.xm,@i from test a,@t b
where b.id=a.pid and @I-1=b.lev
end
return
end
create function c_id(@xm varchar(20)) --找父节点函数
returns @t table(Id int,pid int,xm varchar(20),lev int)
as
begin
insert into @t select id,pid,xm,1 from test
where xm=@xmdeclare @i int 
set @i=1while (@@rowcount>0 and @i>-1)
begin
set @i=@i-1
insert into @t select a.id,a.pid,a.xm,@i from test a,@t b
where b.Pid=a.id and @I+1=b.lev
endreturn
end
select * from dbo.f_id('a1122')
union
select * from dbo.c_id('a1122')

Answer 3

create table Test( 
id int , 
pid int, 
        xm varchar(20) 
) 
insert into test select 1,0,'a1' 
union all select 2,0,'b1' 
union all select 3,0,'c1' 
union all select 4,1,'a11' 
union all select 5,1,'a12' 
union all select 6,2,'b11' 
union all select 7,4,'a111' 
union all select 8,4,'a112' 
union all select 9,8,'a1121' 
union all select 10,8,'a1122' 
union all select 11,8,'a1123' 
union all select 12,10,'a11221' 
union all select 13,10,'a11222' 
union all select 14,10,'a11223' 
union all select 15,12,'a112211' 
union all select 16,12,'a112212' 
union all select 17,15,'a1122111' 
union all select 17,16,'a1122121' --查找父节点
create function f_test(@xm varchar(10))
returns @t table(id int,pid int,xm varchar(10),lev int)
as
begin
declare @lev int
set @lev=1
insert into @t select *,@lev from test where id=(select pid from test where xm=@xm)
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from test a,@t b
where a.id=b.pid and lev=@lev-1
end
return
end--查找子节点
create function f_test_f(@xm varchar(10))
returns @t table(id int,pid int,xm varchar(10),lev int)
as
begin
declare @lev int
set @lev=1
insert into @t select *,@lev from test where pid=(select id from test where xm=@xm)
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from test a,@t b
where a.pid=b.id and lev=@lev-1
end
return
end--调用函数
select xm from dbo.f_test('a1122') where lev<3
union all
select xm from dbo.f_test_f('a1122') where lev<3
order by xm--结果：
xm
a11
a112
a11221
a112211
a112212
a11222
a11223