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

最新推荐文章于 2024-08-23 23:12:59 发布

原创最新推荐文章于 2024-08-23 23:12:59 发布 · 736 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #html #linq

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