您的位置：程序门 -> ms-sql server -> 基础类

递归列出所有名字? 函数如何写?

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

递归列出所有名字? 函数如何写?[已结贴,结贴人:clkun]

发表于：2007-09-03 14:23:41 楼主

我有一无限分类表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

发表于：2007-09-03 14:33:551楼得分:10

找個例子給你吧

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 ')

发表于：2007-09-03 15:14:552楼得分:10

---创建函数
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)

发表于：2007-09-03 15:19:403楼得分:0

---创建测试环境
create table t1（id int,fid int,name varchar（20))
insert t1 select 1, 0, 'a1 '
union all select 2, 1, 'a2 '
union all select 3, 1, 'a3 '
union all select 4, 2, 'a4 '
union all select 5, 3, 'a5 '
union all select 6, 4, 'a6 '
go
select * from t1
go
---创建函数
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)
---删除测试环境
drop table t1
drop function fn_getpath
--结果
/*
---------------------
a5,a3,a1

（所影响的行数为 1 行）

---------------------
a6,a4,a2,a1

（所影响的行数为 1 行）
*/

下一篇：小问题
上一篇：如何读取存储过程中返回的多表集数据