部门层级关系表

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7899089/viewspace-610106/