Ecology E9 查询部门人员信息

select *
from
(
select j.Name,j.Section,
(case when j.Department is null then j.Section
      else j.Department
end 
) as Department,j.Branch,j.Company
from 
(
select g.lastname as Name,g.departmentmark as Section,i.departmentmark as Department,g.subcompanyname as Branch,g.companyname as Company
from 
(
select e.lastname,f.departmentmark,f.supdepid,e.subcompanyname,e.companyname
from 
(
select c.lastname,c.departmentid,c.subcompanyname,d.companyname
from 
 (
select a.lastname,a.departmentid, b.subcompanyname,b.companyid
from dbo.HrmResource a left join dbo.HrmSubCompany b 
on a.subcompanyid1 = b.id
 ) c left join dbo.HrmCompany d
 on c.companyid = d.id
 ) e left join dbo.HrmDepartment f
 on e.departmentid = f.id
 ) g left join dbo.HrmDepartment i
 on g.supdepid = i.id
 ) j
 ) k order by k.Company,k.Branch,k.Department,k.Section