--分部,部门,(人员岗位 -关联部门 对应)
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
人才选拔分析