sql: select employee_number, – 8位员工工号 x_15number, – 15位员工工号 x_number, – 7位员工编号 username, – UASS name, – 员工姓名 hire_date, – 入职日期 department_code, – 部门编码 department_name, – 部门名称 dep_name1, – 部门1级 dep_name2, – 部门2级 dep_name3,-- 部门3级 dep_name4,-- 部门4级 dep_name5, – 部门5级 --20210726 调整多级部门，fromxzf （end） position_name, – 岗位 employee_status, – 员工状态 manager_emp_name, – 汇报上级姓名 manager_employee_number, – 汇报上级8位工号 employee_type_name, – 员工类型 x_yuangongleibie, – 员工类别 x_increasetype, – 增员类别 x_hrbp_temp, – 工作伙伴 array_to_string(array_agg(jobunit),E'\n') jobunit from (WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, tran_dep1.value as name,code, ARRAY [tran_dep1.value] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle, x_jigoucategory from hr_department LEFT join ir_translation AS tran_dep1 ON tran_dep1.res_id = hr_department.id AND tran_dep1.lang='zh_CN' AND tran_dep1.name='hr.department,name' where eroad_index_id is not null and eroad_start_date <= current_date and (eroad_end_date >= current_date or eroad_end_date is null) and not is_virtual_org and status = 'active' and active and parent_department_id is NULL UNION SELECT dep_table.eroad_index_id as cid, tran_dep2.value as name, dep_table.code, dep_level_view.name_list || tran_dep2.value as name_list, dep_level_view.path_ids || dep_table.eroad_index_id as path_ids, dep_table.eroad_index_id = any(dep_level_view.path_ids) as is_cycle, dep_table.x_jigoucategory FROM hr_department dep_table LEFT join ir_translation AS tran_dep2 ON tran_dep2.res_id = dep_table.id AND tran_dep2.lang='zh_CN' AND tran_dep2.name='hr.department,name' JOIN dep_level_view ON dep_table.parent_department_id = dep_level_view.cid and not dep_level_view.is_cycle and dep_table.eroad_index_id is not null and dep_table.eroad_start_date <= current_date and (dep_table.eroad_end_date >= current_date or dep_table.eroad_end_date is null) and not dep_table.is_virtual_org and dep_table.status = 'active' and dep_table.active ) select emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.x_number, – 7位员工编号 emp.username, – UASS emp.name, – 员工姓名 emp.hire_date, – 入职日期 --20210726 调整多级部门，fromxzf （begin） --hr_department.code department_code, – 部门编码 – hr_department.name department_name, – 部门名称 hr_department.code department_code, – 部门编码 array_to_string(hr_department.name_list,'/') department_name, – 部门名称 hr_department.name_list [1] dep_name1, – 部门1级 hr_department.name_list [2] dep_name2, – 部门2级 hr_department.name_list [3] dep_name3,-- 部门3级 hr_department.name_list [4] dep_name4,-- 部门4级 hr_department.name_list [5] dep_name5, – 部门5级 --20210726 调整多级部门，fromxzf （end） hr_position.name position_name, – 岗位 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END employee_status, – 员工状态 manager_emp.name manager_emp_name, – 汇报上级姓名 manager_emp.employee_number manager_employee_number, – 汇报上级8位工号 employee_type_translation.value employee_type_name, – 员工类型 yuangongleibie_selection.name x_yuangongleibie, – 员工类别 increasetype_selection.name x_increasetype, – 增员类别 emp.x_hrbp_temp, – 工作伙伴 person_work_experience.start_date, – 开始日期 person_work_experience.end_date, – 终止日期 person_work_experience.company, – 所在单位 person_work_experience.job, – 岗位 person_work_experience.department, – 部门 person_work_experience.certifier,-- 证明人 person_work_experience.certifier_phone, – 证明人联系方式 person_work_experience.x_zhbianma,-- 总行编码 person_work_experience.x_job,-- 从事工作 person_work_experience.code as expcode, to_char(person_work_experience.start_date,'yyyymm')||'至'|| (case when person_work_experience.start_date is null then '今' else to_char(person_work_experience.start_date,'yyyymm') end)||' ' person_work_experience.company ' ' person_work_experience.department ' ' person_work_experience.job ' ' person_work_experience.x_job jobunit, CASE person_work_experience.x_now WHEN 't' THEN '是' WHEN 'f' THEN '否' ELSE '' END – 是否当前 from hr_employee emp inner join emp_job on emp.id = emp_job.employee_id and emp_job.eroad_index_id IS NOT NULL and emp_job.eroad_start_date <= current_date AND (emp_job.eroad_end_date >= current_date OR emp_job.eroad_end_date IS NULL) AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL --20210726 调整多级部门，fromxzf （begin） – left join hr_department on emp_job.department_id = hr_department.id LEFT JOIN dep_level_view hr_department ON emp_job.department_id=hr_department.cid --20210726 调整多级部门，fromxzf （end） left join hr_position on emp_job.position_id = hr_position.id left join employee_status on emp_job.employee_status = employee_status.id left join hr_employee manager_emp on emp_job.manager_employee_id = manager_emp.id left join employee_type on emp_job.employee_type = employee_type.id left join ir_translation employee_type_translation on employee_type_translation.src=employee_type.name and employee_type_translation.lang='zh_CN' and employee_type_translation.name='employee.type,name' left join res_selection yuangongleibie_selection on emp_job.x_yuangongleibie = yuangongleibie_selection.id left join res_selection increasetype_selection on emp_job.x_increasetype = increasetype_selection.id left join per_person person on emp.person_id = person.id left join person_work_experience on person_work_experience.person_id=person.id where emp.active=True) tall group by employee_number, – 8位员工工号 x_15number, – 15位员工工号 x_number, – 7位员工编号 username, – UASS name, – 员工姓名 hire_date, – 入职日期 department_code, – 部门编码 department_name, – 部门名称 dep_name1, – 部门1级 dep_name2, – 部门2级 dep_name3,-- 部门3级 dep_name4,-- 部门4级 dep_name5, – 部门5级 --20210726 调整多级部门，fromxzf （end） position_name, – 岗位 employee_status, – 员工状态 manager_emp_name, – 汇报上级姓名 manager_employee_number, – 汇报上级8位工号 employee_type_name, – 员工类型 x_yuangongleibie, – 员工类别 x_increasetype, – 增员类别 x_hrbp_temp