树形sql,从子节点查顶级节点的存储过程或sql

树形sql,求一个从子节点查顶级节点的存储过程或sql
id title parentid
1 热映 0
2 电影 1
3 中文 2

从parentid为2的往上查,查到parentid为0的

SQL code

create table tb1(id int,title varchar(100),parentid int)insert tb1(id,title,parentid) values(1,'热映',0);insert tb1(id,title,parentid) values(2,'电影',1);insert tb1(id,title,parentid) values(3,'中文',2); 

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_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
[解决办法]
MSSQL2005及以上版本：
create table t1
(
id int,
title varchar(10),
pid int
)
insert into t1
select 1, '热映', 0 union all
select 2, '电影', 1 union all
select 3, '中文', 2
select * from t1

;with aaa as
(
select * from t1 where pid=2
union all
select a.* from t1 as a inner join aaa as b on a.id=b.pid
)
select * from aaa

-----------------------
idtitlepid
3中文2
2电影1
1热映0
[解决办法]
探讨


能不能只取最后一条数据,只要顶级节点,不要中间的