代码
--无限循环父子树
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
*/