递归列出所有名字? 函数如何写?
我有一无限分类表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)
