求一SQL语句,或SQL函数

[unwonted]

现要进行一个查询,有两个字段,为'本层'(id),'上一层'(pid);
怎样知道一个id,把录属于id 的所有上级的层都查询出来??

[unwonted]

更正,应该是知道一id,查询录属于它的所有下级层(包括录属于它下级的下级的所有层)

[samfeng_2003]

给你列个例子吧！

[create] table t(id int,name char(1),parent_id int,sfmx int)
insert into t select 1,'a',0,0
insert into t select 2,'b',1,0
insert into t select 3,'c',1,0
insert into t select 4,'d',1,1
insert into t select 5,'e',2,1
insert into t select 6,'f',3,1
go

select a.id,isnull(c.name+'-','')+isnull(b.name+'-','')+isnull(a.name,'')
from t a
left join t b on a.parent_id=b.id
left join t c on b.parent_id=c.id




drop table t

[unwonted]

我已解决这问题了,谢谢你的提示,查询方法为:
select distinct a.id from tb a left join tb b on a.pid=b.id where a.pid=@***
union
select distinct a.id from tb a left join tb b on a.pid=b.id where b.pid=@***