一个游标查询的问题，请高手赐教！
数据库中有表：
ID       PID
1           0
2           0
3           1
4           2
5           3
6           4

PID是ID的父目录，PID=0表示该目录最高级
现在问：如何输入 "5 "得到他的最高父ID：1（5-> 3-> 1）？
也就是说，输入 "6 "会得到2
谢谢

------解决方案--------------------
create table #(ID int, PID int)
insert into # select 1, 0 union all
select 2 , 0 union all
select 3 , 1 union all
select 4 , 2 union all
select 5 , 3 union all
select 6 , 4

declare @a int
declare @b int
set @a=6
while @@rowcount> 0
begin
set @b=@a
select @a=pid from # where id=@a and pid <> 0
end

print @b

--
2
------解决方案--------------------
不要用游標，寫個函數

--建立測試環境
Create Table TEST
(ID Int,
PID Int)
Insert TEST Select 1, 0
Union All Select 2, 0
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 3
Union All Select 6, 4
GO
--建立函數
Create Function Get_PID(@ID Int)
Returns Int
As
Begin
While @@ROWCOUNT > 0
Select @ID = PID From TEST Where ID = @ID And PID != 0
Return @ID
End
GO
--測試
Select dbo.Get_PID(5) As PID
Select dbo.Get_PID(6) As PID
GO
--刪除測試環境
Drop Table TEST
Drop Function Get_PID
--結果
/*
PID
1

PID
2
*/
------解决方案--------------------
create table t([ID] int,pID int )
insert into t
select 1, 0
union select 2, 0
union select 3, 1
union select 4, 2
union select 5, 3
union select 6, 4
select * from t
--
go
alter function gettop(@ID int)
returns int
as
begin
declare @pid int
select @pid=pid from t
if @pid=0
return @id
else
begin
select @pid=pid from t where [id]=@id
while @pid <> 0
begin
select @id=@pid
select @pid=pid from t where [id]=@id
end
end
return @id
end
go
select dbo.gettop(6)

drop table t
drop function gettop
------解决方案--------------------
create table T(ID int, PID int)
insert T select 1, 0
union all select 2, 0
union all select 3, 1
union all select 4, 2
union all select 5, 3
union all select 6, 4

create function f_pid(@ID int)
returns @t_level table(ID int, Level int)
as
begin
declare @level int
set @level=1
insert @t_level select @ID, @level
while @@rowcount> 0
begin
set @level=@level+1
insert @t_level select a.PID, @level
from T a, @t_level b
where a.ID=b.ID
and b.Level=@level-1
end

return
end
------解决方案--------------------
--创建测试环境
create table t(ID int,PID int)

--插入测试数据
insert t(ID,PID)
select '1 ', '0 ' union all
select '2 ', '0 ' union all
select '3 ', '1 ' union all
select '4 ', '2 ' union all
select '5 ', '3 ' union all