递规写个小存储过程

 

表结构及数据如下：
Dept   ParentDept
A0401    A0400
A0400    C0300
C0300    B0200
B0200    0
E0501    E0500
E0500    E0101
......

查询条件为Dept='A0401'，要得到如下的数据
A0401
A0400
C0300
B0200

create table t_1(dept varchar(10),parentdept varchar(10))
insert into t_1(dept,parentdept)
select 'A0401','A0400'
union all select 'A0400','C0300'
union all select 'C0300','B0200'
union all select 'B0200','0'
union all select 'E0501','E0500'
union all select 'E0500','E0101'

--递归取下级
create function f_getchild(@dept varchar(10))
returns @t table(dept varchar(10))
as
begin
declare @t_temp table(id int identity(1,1),child varchar(10))
insert into @t(dept)
select parentdept from t_1 where dept = @dept and parentdept<>'0'

insert into @t_temp(child)
select parentdept from t_1 where dept = @dept and parentdept<>'0'

declare @child_temp  varchar(10),@max_id  int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(dept)
select * from dbo.f_getchild(@child_temp)
select @min_id = @min_id + 1
end
return
end
--调用
select 'A0401'
union all
select dept from dbo.f_getchild('A0401')

--建立测试环境
create table PersonDept(Dept varchar(10),ParentDept varchar(10))
insert into PersonDept
select 'A0401','A0400' union all
select 'A0400','C0300' union all
select 'C0300','B0200' union all
select 'B0200','0' union all
select 'E0501','E0500' union all
select 'E0500','E0101'

--测试
--建立Function
/*
用递归处理树型结构(表结构)
递归求城市，从小到大的，或从大到小。
*/
CREATE function GetDept(@iDeptID varchar(20))
returns nvarchar(1000)
as
begin
declare @vReturnValue nvarchar(1000)
,@iParentID varchar(20)
,@vCurrentDeptName nvarchar(200)
select @vReturnValue=''
,@vCurrentDeptName=''
if(exists(select top 1 0 from PersonDept where Dept=@iDeptID  and ParentDept='0'))
begin
select @vReturnValue=@vReturnValue+Dept
from PersonDept
where Dept=@iDeptID
and ParentDept='0'
end
else
begin
select @iParentID=ParentDept
,@vCurrentDeptName=Dept
from PersonDept
where Dept=@iDeptID
if(@vCurrentDeptName='')
begin
--set @vReturnValue=stuff(@vReturnValue,len(@vReturnValue),1,'')
set @vReturnValue=@vReturnValue+isnull(@iParentID,'')
--return(@vReturnValue)
end
else
begin
set @vReturnValue=@vReturnValue+@vCurrentDeptName+'-'+dbo.GetDept(@iParentID)--从子到父
end
--set @vReturnValue=@vReturnValue+dbo.GetDept(@iParentID)+'-'+@vCurrentDeptName--从父到子
end
return (@vReturnValue)
end

select *,dbo.GetDept(dept) AllDept from PersonDept
--显示结果
DeptParentDeptAllDept
A0401A0400A0401-A0400-C0300-B0200
A0400C0300A0400-C0300-B0200
C0300B0200C0300-B0200
B02000B0200
E0501E0500E0501-E0500-
E0500E0101E0500-

--删除测试环境
drop table PersonDept

返回一个table
/*
用递归处理树型结构(表结构)
递归求城市，从小到大的，或从大到小。
*/
CREATE function GetTableDept(@iDeptID varchar(20))
returns  @tDept table(Dept varchar(20))
as
begin
 declare @vReturnValue nvarchar(1000)
 ,@iParentID varchar(20)
 ,@vCurrentDeptName nvarchar(200)
 ,@iEnd int
 select @vReturnValue=''
 ,@vCurrentDeptName=''
 ,@iEnd=1

 if(exists(select top 1 0 from PersonDept where Dept=@iDeptID  and ParentDept='0'))
 begin
  set @iEnd=0
  insert into @tDept(Dept) values(@vCurrentDeptName)
  return
 end
 
 select @vCurrentDeptName=Dept
 ,@iParentID=ParentDept
 from PersonDept
 where Dept=@iDeptID

 insert into @tDept(Dept) values(@vCurrentDeptName)
 insert into @tDept(Dept) values(@iParentID)

 while(2>1)
 begin
  select @iParentID=ParentDept
  from PersonDept
  where Dept=@iParentID

  insert into @tDept(Dept) values(@iParentID)

  if(exists(select top 1 0 from PersonDept where Dept=@iParentID  and ParentDept='0'))
  begin
   break
  end
  if(not exists(select top 1 0 from PersonDept where Dept=@iParentID))
  begin
   break
  end
 end
 return
end

----->

select * from dbo.GetTableDept('A0401')