求一生成路径的SQL语句

[vfp_system]

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


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

[Well]

用递归可以做到。

[DengXingJie]

這就像操作BOM表一樣

[gahade]

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

[Well]

--建立测试环境
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
--显示结果
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

[copico]

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

SELECT * FROM #tempDD


DROP TABLE #tempDD

[copico]

上面有一点需要改进

/*
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'
END

SELECT * FROM #tempDD


DROP TABLE #tempDD

[Well]

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

[enterala]

学习