递归列出所有名字? 函数如何写?
我有一无限分类表t1(id,fid,name)

  id   fid   name
    1     0       a1
    2     1       a2
    3     1       a3
    4     2       a4
    5     3       a5
    6     4       a6

      传入id     能列出   他所有父对应的name,如id=6   ,对应父name:   a6,a4,a2,a1


------解决方案--------------------
找個例子給你吧


create table ps_mstr(ps_par varchar(10)collate Latin1_General_BIN,ps_comp varchar(10),qty numeric(9,2)

)

--sp_help 'ps_mstr '
insert into ps_mstr
select 'FG001 ', 'SFG001 ', 1 union all
select 'FG001 ' , 'SFG002 ', 1 union all
select 'FG001 ' , 'SFG003 ', 1 union all
select 'SFG001 ', 'WIP001 ', 2 union all
select 'SFG001 ' , 'WIP002 ', 2 union all
select 'SFG002 ' , 'WIP003 ', 3 union all
select 'SFG002 ' , 'WIP004 ', 3 union all
select 'SFG002 ' , 'WIP005 ', 2 union all
select 'SFG003 ' , 'WIP006 ', 3 union all
select 'WIP001 ' , 'RAW001 ', 2.66 union all
select 'WIP001 ' , 'RAW002 ' , 2.33 union all
select 'WIP002 ' , 'RAW003 ' , 3.21 union all
select 'WIP003 ' , 'RAW004 ' , 1.89 union all
select 'WIP003 ' , 'RAW005 ' , 1.86


create function f_cid(@ps_par varchar(10))
returns @t_level table(ps_par varchar(10)collate Latin1_General_BIN,ps_comp varchar(10),qty numeric(9,2),level int)
as
begin
declare @level int
set @level=1
insert into @t_level select ps_par,ps_comp,qty,@level from ps_mstr where ps_par=@ps_par collate Latin1_General_BIN
while @@rowcount> 0
begin
set @level=@level+1
insert into @t_level select a.ps_par, a.ps_comp,a.qty*b.qty,@level
from ps_mstr a,@t_level b
where a.ps_par=b.ps_comp collate Latin1_General_BIN--(秶俶齬唗)
and b.level=@level-1
end

return
end

Go
select * from f_cid( 'FG001 ')

------解决方案--------------------
---创建函数
Create Function Fn_GetPath(@id int)
Returns Varchar(8000)
As
begin
Declare @S Varchar(8000),@Name Varchar(20)
Set @S= ' '
Select @Name=[Name]+ ', ' From t1 Where id=@id
Select @id=fid From t1 Where id=@id
While @@RowCount> 0
Begin
Select @S=@S+ ', '+[Name] From t1 Where id=@id
Select @id=fid From t1 Where id=@id
End
Return @Name+Stuff(@S,1,1, ' ')
End
Go
---调用自定义函数
Select dbo.Fn_GetPath(5)
Select dbo.Fn_GetPath(6)