根据子节点 获取它的所有的父节点解决办法

根据子节点 获取它的所有的父节点
表结构如下:

id int 主键

name varchar(50)

parentid int 

id name parentid 

1 a1aa 0

2 bbb 1
3 af 2
4 aff 2
5 aaa 1
6 asa 4

这个分类是无限极,请给出sql 语句,谢谢

[解决办法]

SQL code

-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate table tb(ID int, ParentID int)insert into tb select 1,0  insert into tb select 2,1  insert into tb select 3,1  insert into tb select 4,2  insert into tb select 5,3  insert into tb select 6,5  insert into tb select 7,6-->Title:查找指定節點下的子結點if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin   insert @t select ID from tb where ParentID=@ParentID   while @@rowcount<>0   begin      insert @t select a.ID from tb a inner join @t b      on a.ParentID=b.id and       not exists(select 1 from @t where id=a.id)   end returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin   insert @t select ParentID from tb where ID=@ID   while @@rowcount!=0   begin     insert @t select a.ParentID from tb a inner join @t b       on a.id=b.ParentID and        not exists(select 1 from @t where ParentID=a.ParentID)   end  returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
[解决办法]
SQL code
;with cte as(select * from tb where name='asa' union all select a.* from tb a,cte b where a.id=b.parentid)select * from cte order by id
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([id] int,[name] varchar(4),[parentid] int)insert [tb]select 1,'a1aa',0 union allselect 2,'bbb',1 union allselect 3,'af',2 union allselect 4,'aff',2 union allselect 5,'aaa',1 union allselect 6,'asa',4go;with cte as(select * from tb where name='asa' union all select a.* from tb a,cte b where a.id=b.parentid)select * from cte order by id/**id          name parentid----------- ---- -----------1           a1aa 02           bbb  14           aff  26           asa  4(4 行受影响)**/
[解决办法]
SQL code
--> 测试数据：#testif object_id('tempdb.dbo.#test') is not null drop table #testcreate table #test([id] int,[name] varchar(4),[parentid] int)insert #testselect 1,'a1aa',0 union allselect 2,'bbb',1 union allselect 3,'af',2 union allselect 4,'aff',2 union allselect 5,'aaa',1 union allselect 6,'asa',4declare @id intset @id=6;with tas(select * from #test where ID=@idunion allselect a.* from #test ainner join t bon a.id=b.parentid)select parentid from t/*parentid4210*/