sql如下： select emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.name, – 员工姓名 per_national_id.national_id, – 身份证号码 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' when 'Pre-hire' then '预入职' when 'Abandonment' then '放弃入职' ELSE '' END employee_status, – 员工状态 employee_resignation.confirmed_resignation_date,-- 离职生效日期 resignation_type.name x_reducetype, – 减员类型 x_reducereason, – 减员原因 resignation_reason.name resignation_reason, – 离职原因类型 x_dimission_to, – 离职去向 ir_translation_quxiang.value x_lizhiquxiangleixing, – 离职去向类型 employee_resignation.notes, – 离职原因说明 case when is_event_done='t' then '已审批' when is_event_done='f' then '未审批' else '' end is_event_done, – 是否审批 array_to_string(dep_full.dep_full_name,'/') dep_full_name, – 部门 emp_job.eroad_start_date, – 雇佣信息生效日期 case when emp_job.eroad_end_date is null then '2999-01-01' else emp_job.eroad_end_date end – 雇佣信息失效日期 from employee_resignation left join hr_employee emp on employee_resignation.employee_id=emp.id left join per_person person on emp.person_id = person.id left join per_national_id on person.id = per_national_id.person_id left join emp_job on emp_job.employee_id=emp.id left join employee_status on emp_job.employee_status = employee_status.id left join resignation_type on employee_resignation.resignation_type=resignation_type.id left join res_selection quxiang_selection on employee_resignation.x_lizhiquxiangleixing=quxiang_selection.id left join ir_translation ir_translation_quxiang on quxiang_selection.id=ir_translation_quxiang.res_id and ir_translation_quxiang.name='res.selection,name' left join resignation_reason on employee_resignation.resignation_reason=resignation_reason.id left join (select id, dep_id, dep_name, dep_code, x_jigoucategory, case when (dep_array::varchar[]) [1] =dep_name then dep_array else dep_array||array [dep_name] end dep_full_name from (WITH RECURSIVE subordinates (id,dep_id,dep_name,dep_code,dep_array,x_jigoucategory) AS ( SELECT id, id dep_id, name dep_name, code dep_code, array [name] dep_array, x_jigoucategory FROM hr_department WHERE id = (select hr_department.eroad_index_id from hr_department where hr_department.eroad_start_date<=current_date and (hr_department.eroad_end_date>=current_date or hr_department.eroad_end_date is null) and hr_department.active=True and eroad_current_record_id is null and parent_department_id is null and status='active' and code='JXJK0001') UNION all SELECT s.id, eroad_index_id dep_id, name dep_name, code dep_code, case when s.dep_name='建信金融科技有限责任公司' then array [s.dep_name] else s.dep_array||array [s.dep_name] end dep_array, s.x_jigoucategory FROM hr_department e INNER JOIN subordinates s ON s.dep_id = e.parent_department_id where e.eroad_start_date<=current_date and (e.eroad_end_date>=current_date or e.eroad_end_date is null) and e.active=True and e.eroad_current_record_id is null ) select * from subordinates order by rpad(dep_code::varchar,100,'0') asc) dep_all) dep_full on emp_job.department_id=dep_full.dep_id where per_national_id.is_primary = True or per_national_id.person_id is null and emp_job.active=TRUE and emp_job.EROAD_END_DATE<=employee_resignation.confirmed_resignation_date and emp_job.EROAD_END_DATE is not null