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