泛微： 分部,部门，岗位--拼接SQL

--分部,部门,(人员岗位 -关联部门 对应)
select HrmDepartment.id  HrmDepartmentIdd,HrmDepartment.canceled,
       subcompanyname,
       departmentname,
       jobtitlename,
       departmentcode,
       HrmJobTitlesIdd,jobgroupid,jobgroupname,
       jobactivityid,jobactivityname,
       HrmJobTitlesId
from     HrmDepartment
left join HrmSubCompany on  HrmDepartment.subcompanyid1=HrmSubCompany.id
left join (
       --人力资源岗位表  jobactivityid所属职责id(人力资源职责表   hrmjobactivities)
        select HrmJobTitles.id  HrmJobTitlesIdd ,hrmjobactivities.jobgroupid  ,jobgroupname, 
               HrmJobTitles.jobactivityid,jobactivityname,
               HrmJobTitles.id HrmJobTitlesId,jobtitlename,
               departmentid,
               dbo.HrmJobTitles.sortid
         from HrmJobTitles  --[人力资源岗位表]  HrmJobTitles
           left join HrmJobActivities on HrmJobTitles.jobactivityid=hrmjobactivities.id  --[人力资源职责表] HrmJobActivities
           left join HrmJobGroups on hrmjobactivities.jobgroupid=hrmjobgroups.id  --[人力资源职务类型表]hrmjobgroups
           left join v_HrmResource_gangwei on v_HrmResource_gangwei.jobtitle=HrmJobTitles.id
         --order by hrmjobactivities.jobgroupid, HrmJobTitles.jobactivityid
           ) a   on  HrmDepartment.id = a.departmentid
where  HrmDepartment.canceled is null
order by HrmDepartment.subcompanyid1,departmentcode,sortid

人才选拔分析