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); 

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create 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_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
   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
  return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/



本文来自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

-----------------------
id	title	pid
3	中文	2
2	电影	1
1	热映	0

------解决方案--------------------
探讨


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