无限循环父子树(主要是数据库里面的一张表，然后根据ID查找父类的名称，以及查找子类的名称) 自己思考，存点东西。...

 

代码

--无限循环父子树
create table Father(Fid int identity(1,1),Fname nvarchar(20),FatherId int)
insert into Father
select '数据库开发',0 union all
select 'MS-SQL SERVER',1 union all
select '基础类',1 union all
select '应用实例',1 union all
select 'BI',1 union all
select 'BISS',5 union all
select 'BIAA',6 union all
select 'Oracle',0 union all
select 'VFP',8 union all
select 'Access',8 union all
select 'Sybase',8 union all
select 'MySql',8 union all
select 'MySqlss',12
select * from Father
/*
Fid Fname FatherId
----------- -------------------- -----------
1 数据库开发 0
2 MS-SQL SERVER 1
3 基础类 1
4 应用实例 1
5 BI 1
6 BISS 5
7 BIAA 6
8 Oracle 0
9 VFP 8
10 Access 8
11 Sybase 8
12 MySql 8
13 MySqlss 12
*/
select * from Father where FatherId=0
/*
Fid Fname FatherId
----------- -------------------- -----------
1 数据库开发 0
8 Oracle 0
*/
-- 一、知道子类ID找父类名称
--1、函数
alter function f_getParent(@fid int)
returns varchar(40)
as
begin
declare @ret varchar(40)

while exists(select 1 from Father where Fid=@fid and FatherId<>0)
begin
select @fid=b.Fid,@ret=','+rtrim(b.Fname)+isnull(@ret,'')
from
Father a,Father b
where
a.Fid=@fid and b.Fid=a.FatherId
end

set @ret=stuff(@ret,1,1,'')
return @ret
end
go
select Fid,isnull(dbo.f_getParent(Fid),'根目录') as Fname from Father
/*
Fid Fname
----------- ------------------
1 根目录
2 数据库开发
3 数据库开发
4 数据库开发
5 数据库开发
6 数据库开发,BI
7 数据库开发,BI,BISS
8 根目录
9 Oracle
10 Oracle
11 Oracle
12 Oracle
13 Oracle,MySql
*/
--2、存储过程
alter procedure f_getParents
@fid int,
@parent nvarchar(100) out
as
begin

while exists(select 1 from Father where Fid=@fid and FatherId<>0)
begin
select @fid=b.Fid,@parent=','+rtrim(b.Fname)+isnull(@parent,'')
from
Father a,Father b
where
a.Fid=@fid and b.Fid=a.FatherId
end

set @parent=stuff(@parent,1,1,'')
select @parent
end
go
exec f_getParents 13,''--查MySqlss父目录的名称
/*
--------------------------
Oracle,MySql
*/

-- 二、知道父类ID找所属子类名称
alter procedure f_getSons
@FatherId int,
@sons nvarchar(100) out
as
begin
while exists(select 1 from Father where FatherId=@FatherId)
begin
select @FatherId=b.Fid,@sons=','+rtrim(b.Fname)+isnull(@sons,'')
from
Father a,Father b
where a.Fid=@FatherId and b.FatherId=a.Fid
end
set @sons=stuff(@sons,1,1,'')
set @sons=isnull(@sons,'无子类')
select @sons
end
go

exec f_getSons 1,''
/*
-----------------------------------------
BIAA,BISS,BI,应用实例,基础类,MS-SQL SERVER
*/

 

转载于:https://www.cnblogs.com/sibiyellow/archive/2010/10/08/1846018.html