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

-----------------------------------用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 @lvlbegin
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/，如需轉載，請註明出處，否則將追究法律責任。