sqlserver 如何获取子节点的所有父节点？

[netboygg]

现有一张表其中[controlid]为节点id,[controlparentid]为改节点的父节点id
如何查出子节点的所有父节点？[controlid]为自动增长int
谢谢了~！！！

CREATE TABLE test(
	[controlid] [int] IDENTITY(1,1) NOT NULL,
	[controlparentid] [int] NULL,
	[controlname] [varchar](300) NULL
)

INSERT INTO test ([controlparentid],[controlname])VALUES (1,'test1')
INSERT INTO test ([controlparentid],[controlname])VALUES (1,'test2')
INSERT INTO test ([controlparentid],[controlname])VALUES (2,'test3')
INSERT INTO test ([controlparentid],[controlname])VALUES (3,'test4')
INSERT INTO test ([controlparentid],[controlname])VALUES (4,'test5')
--...............................................................

[jiangshun]

DECLARE @controlid INT SET @controlid=3 ;WITH temp AS ( SELECT * FROM test WHERE [controlid]=@controlid UNION ALL SELECT t.* FROM test t INNER JOIN temp t2 ON t2.controlparentid=t.[controlid] ) SELECT * FROM temp t

[开着拖拉机泡妞]

-->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_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
   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 
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->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
*/