求一存储过程 ，求所有父ID
一无限级分类表
ID         parentID
1               0
2               0
3               0
4               1
5               3
6               4
7               6

求某一ID下的所有父ID    
如：7   有   1，4，6   三个父ID
1--> 4--> 6--> 7

请问怎么写

------解决方案--------------------
create table xyz(ID int, parentID int)
insert xyz select 1, 0
union all select 2 ,0
union all select 3 ,0
union all select 4 ,1
union all select 5 ,3
union all select 6 ,4
union all select 7 ,6

go
create function get_xyz(@id int)
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=ltrim(@id)
while exists(select 1 from xyz where id=@id)
begin
select @id=parentID from xyz where id=@id
select @a=@a+ '> -- '+ltrim(@id)
end
return @a
end
go
select reverse(dbo.get_xyz(6))
------解决方案--------------------
create proc parentID
(
@ID int
)
as

select * into #t
from t
where id=@id

while @@rowcount> 0
begin
insert #t
select * from t
where id=(select top 1 parentID from #t order by id)
end

select * from #t
order by id

--结果：
id parentid
----------- -----------
1 0
4 1
6 4
7 6
------解决方案--------------------
drop function uf_getpath
go
create function dbo.uf_getpath(@parentID int)
returns @t table (id int)
as
begin
declare @id int
select @ID = parentID from test where id = @parentID
while @@rowcount > 0 and @ID <> 0
begin
insert into @t(id)
select @id
select @ID = parentID from test where id = @ID
end
insert into @t(id)
select @parentID
return
end
GO

select * from uf_getpath(7) t order by id
------解决方案--------------------
wcfboy1(王风) ( ) 信誉：100 2007-07-18 14:58:52 得分: 0


把1，4，6，7存在表A里
select * from A 这样来取各个值


-----------------------------------------------------
--得到所有的父節點

--建立測試環境
Create Table TEST(ID Int, ParentID Int)
Insert Into TEST Select 1,0
Union All Select 2,0
Union All Select 3,0
Union All Select 4,1
Union All Select 5,3
Union All Select 6,4
Union All Select 7,6
GO
--建立函數
Create Function F_GetParent(@ID Int)
Returns @Tree Table (ID Int, ParentID Int)
As
Begin
Insert @Tree Select * From TEST Where ID = @ID
While @@Rowcount > 0
Insert @Tree Select A.* From TEST A Inner Join @Tree B On A.ID = B.ParentID And A.ID Not In (Select ID From @Tree) Where A.ParentID Is Not Null
Return
End
GO
--測試
Select ID From dbo.F_GetParent(7) Order By ID
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetParent
--結果
/*
ID
1
4
6
7
*/