数据库同一张表结构拼接
updept是上级部门
当我输入一个部门时,where updept=上级部门
然后列出下级部门(dept_code),然后将(dept_code)在放到updept中找，以此循环递归
最后返回部门树
A部门，AA部门，AB部门，AC部门，B部门，BA部门，C部门.....
[解决办法]
如：
--无限循环父子树
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
*/
--父类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
*/
自己参考下 修改.
