转CSDN关于显示目录父子表算法！！！

 

一个部门的数据表,里面是目录树结构.
如:  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