转优快云关于显示目录父子表算法！！！

 

一个部门的数据表,里面是目录树结构.
如:  deptID  deptName  parentID
里面的数据  是     1    所有部门   0
                   2    财务部     1
                   3    市场部     1
                   4    仓库管理   1
                   5    东北市场   3
                   6    西北市场   3
                   7    东南市场   3
                   8    上海市     7
                   9    北京市     6

等依次类推得目录树结构
我想写一个函数 传入部门ID号后 马上得到相应的 部门结构 如
输入8   得到的是    市场部-东南市场-上海市
输入9   得到的是    市场部-西北市场-北京市
输入6   得到的是    市场部-西北市场

输入3   得到的是    市场部

输入1   得到的是    所有部门
--------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
所要部门不要显示吗~~~

create table test(deptID int,deptName varchar(20),parentID int) ----------------------建测试表
insert test
select 1    ,'所有部门',   0 union all
select 2    ,'财务部',     1 union all
select 3    ,'市场部',     1 union all
select 4    ,'仓库管理',   1 union all
select 5    ,'东北市场',   3 union all
select 6    ,'西北市场',   3 union all
select 7    ,'东南市场',   3 union all
select 8    ,'上海市',     7 union all
select 9    ,'北京市',     6
--select * from test
go

create proc pTest           -------------- 建存储过程，外面参数不用括号(),而FUNCTION要（）
    @id int                       ---------------输入参数，来传递查询部门号
as
begin
    declare @result varchar(1000) -------------返回的结果
    declare @ProvID int       --------------存储过程内部的参数来，来匹配外面的@id          
    select @ProvID=@id      --------------FU值

    select @result=deptName from test where deptID=@ProvID -----------FU值计算
    while exists(select 1 from test where deptID=@ProvID and parentID<>0) ----------------加一个CIRCLE条件，匹配出有上属部门的--                                                                                                    - --- -- 记录(parentid<>0)
    begin
        select @ProvID=parentID from test where deptID=@ProvID -------------把上属部门的部号号FU值给自己
        select @result=deptName +'-'+@result from test where deptID=@ProvID
    end

    select @result
end
go

exec pTest 3

drop proc pTest
drop table test
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
BEGIN TRAN --------------------------以事务方式
GO
CREATE TABLE tbDept
(
deptID INT,
deptName VARCHAR(50),  
parentID INT
)

INSERT INTO tbDept
SElECT 1,'所有部门',0 UNION ALL
SElECT 2, '财务部',  1 UNION ALL
SElECT 3, '市场部',  1 UNION ALL
SElECT 4, '仓库管理',1 UNION ALL
SElECT 5, '东北市场',3 UNION ALL
SElECT 6, '西北市场',3 UNION ALL
SElECT 7, '东南市场',3 UNION ALL
SElECT 8, '上海市',  7 UNION ALL
SElECT 9, '北京市',  6

SELECT *
FROM tbDept

GO
CREATE FUNCTION GetDeptStruct
(@deptID INT)
RETURNS VARCHAR(500)
AS 
BEGIN

DECLARE @s VARCHAR(500), @dID INT

SELECT @s = deptName, @dID = @deptID -----------FU值查找匹配
FROM tbDept
WHERE deptID = @deptID

WHILE @@ROWCOUNT > 0--------- 上面的语句找到，开始CIRCLE
BEGIN
SELECT @s = b.deptName + '-' + @s, @dID = b.deptID  ------------等于把上属部门号FU值给自己 
FROM tbDept a 
INNER JOIN tbDept b ON a.parentID = b.deptID----------------------------a,子表，b, 上级表
WHERE a.deptID = @dID ------------------------------------------------------------同上匹配
AND b.deptID > 1             ----------------------------------------------------------------
END

RETURN @s

END
GO

SELECT dbo.GetDeptStruct(2)

GO
ROLLBACK