求一生成路径的SQL语句

Question

表结构及数据如下：
Dept   ParentDept
A0401    A0400
A0400    C0300
C0300    B0200
B0200    0
E0501    E0500
E0500    E0101
......
查询条件为Dept='A0401'，要得到如下的数据
A0401
A0400
C0300
B0200

Answer 1

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')

Answer 2

--建立测试环境
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)
endselect *,dbo.GetDept(dept) AllDept from PersonDept
--显示结果
Dept ParentDept AllDept
A0401 A0400 A0401-A0400-C0300-B0200
A0400 C0300 A0400-C0300-B0200
C0300 B0200 C0300-B0200
B0200 0 B0200
E0501 E0500 E0501-E0500-
E0500 E0101 E0500---删除测试环境
drop table PersonDept

Answer 3

CREATE TABLE #tempDD(
Dept varchar(10)
)DECLARE @KEYWORD VARCHAR(10)SET @KEYWORD = 'A0401'
WHILE (@KEYWORD <> 'ERROR')
BEGIN INSERT INTO #tempDD VALUES(@KEYWORD) IF EXISTS ( SELECT ParentDept FROM DD WHERE Dept = @KEYWORD )
SELECT @KEYWORD = ParentDept FROM DD WHERE Dept = @KEYWORD
ELSE
SET @KEYWORD = 'ERROR'
ENDSELECT * FROM #tempDD
DROP TABLE #tempDD

Answer 4

上面有一点需要改进/*
CREATE TABLE DD(
Dept varchar(10),
ParentDept varchar(10)
)
*/CREATE TABLE #tempDD(
Dept varchar(10)
)DECLARE @KEYWORD VARCHAR(10)SET @KEYWORD = 'A0401'
WHILE (@KEYWORD <> 'ERROR')
BEGIN IF EXISTS ( SELECT ParentDept FROM DD WHERE Dept = @KEYWORD )
BEGIN
INSERT INTO #tempDD VALUES(@KEYWORD)
SELECT @KEYWORD = ParentDept FROM DD WHERE Dept = @KEYWORD
END

ELSE
SET @KEYWORD = 'ERROR'
ENDSELECT * FROM #tempDD
DROP TABLE #tempDD

Answer 5

返回一个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')