ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 帶有樹形結構的部門層級關系表

帶有樹形結構的部門層級關系表

原创 Linux操作系统 作者:keeking 时间:2009-08-01 15:49:50 0 删除 编辑

-----------------------------------用CTE

WITH DeptTree(ParentDeptID, DeptID, DeptName, DeptLevel,Sort)
AS
(
SELECT PMIS_Department_Name as ParentDeptID,
autoid as DeptID,
PMIS_Department_Code as DeptName,
0 AS DeptLevel,
--根路徑
Cast(PMIS_Department_Code+'->' as varchar(max)) as Sort
FROM dbo.PMIS_Department
WHERE PMIS_Department_Name =0
UNION ALL
SELECT c.PMIS_Department_Name,
c.autoid,
c.PMIS_Department_Code,
p.DeptLevel + 1,
--子路徑=根路徑+孩子的DEP
Cast(p.Sort+c.PMIS_Department_Code+'->' as varchar(max))
FROM dbo.PMIS_Department c
INNER JOIN DeptTree p
ON c.PMIS_Department_Name = p.DeptID
)
SELECT ParentDeptID, DeptID, DeptName,replicate('    ',DeptLevel)+'|__'+DeptName  as dep, DeptLevel,Sort
FROM DeptTree order by sort

 

----------------------用函數的腳本

alter function dbo.GDS_TEST
(@root int,@maxlevels as int=null)
returns @Subs table( depid int,depName varchar(50),lvl int ,path varchar(max)
unique clustered(lvl,depid))
as
begin
declare @lvl int;
set @lvl=0
set @maxlevels=coalesce(@maxlevels,2147483647)
insert into @Subs(depid,depName,lvl,path)
select autoid,PMIS_Department_Code,@lvl,cast(PMIS_Department_Code as varchar(10))+'>' from dbo.PMIS_Department
where autoid=@root
while @@rowcount>0--當上一級別包含行
and @lvl<@maxlevels--且上一級別小于最大level
begin
select @lvl=@lvl+1  --遞增級別計數器
insert into @Subs(depid,depName,lvl,path)
select c.autoid,replicate('     ',@lvl)+'|__'+c.PMIS_Department_Code,@lvl,p.path+cast(c.PMIS_Department_Code as varchar(10))+'>'
 from @Subs as p --父級
join dbo.PMIS_Department as c --子級
on p.lvl=@lvl-1 and c.PMIS_Department_Name=p.depid  --從上一級篩選父親
end

return
end

select * from dbo.GDS_TEST(3,2)order by path

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-611048/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 頁面表格填寫
请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    241498