部門層級關係表

select a.ID,a.Grade,a.DepName,
case when (b.Grade='Grade1' or b.Grade='Grade2') then a.DepName
else coalesce(b.DepName,a.DepName) end
as ParentDep,
case when c.DepName is null then a.DepName  
when c.DepName='GDSBG' then b.DepName else c.DepName end
as BU,
coalesce(d.DepName,c.DepName,b.DepName,a.DepName) as BG
from
(select autoid as ID,PMIS_Department_code as DepName,PMIS_Department_Type as Grade,PMIS_Department_Name as ParentID
FROM PMIS_Department) a
left join
( select autoid as ID,PMIS_Department_code as DepName,PMIS_Department_Type as Grade,PMIS_Department_Name as ParentID
FROM PMIS_Department) b
on a.ParentID=b.ID
left join
(select autoid as ID,PMIS_Department_code as DepName,PMIS_Department_Type as Grade,PMIS_Department_Name as ParentID
FROM PMIS_Department)c
on b.ParentID=c.ID
left join
(select autoid as ID,PMIS_Department_code as DepName,PMIS_Department_Type as Grade,PMIS_Department_Name as ParentID
FROM PMIS_Department) d
on c.ParentID=d.ID

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-610106/，如需轉載，請註明出處，否則將追究法律責任。