select distinct * 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 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 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, – 员工工号 emp.name AS emp_name, – 员工姓名 per_nati.national_id, – 证件号码 bank_information.bank_account, – 银行账户 bank_information.bank_name, – 开户行（支行) bank_information1.bank_account as bank_account1, --副卡账号 bank_information1.bank_name as bank_name1, --副卡银行名 emp.business_phone, – 手机号码 tran_emp_type.value AS emp_type, – 员工类型 dep_level_view.name_list [1] AS "一级组织单元名称", – 一级组织单元名称 dep_level_view.name_list [2] AS "二级组织单元名称", – 二级组织单元名称 dep_level_view.name_list [3] AS "三级组织单元名称", dep_level_view.name_list [4] AS "四级组织单元名称", dep_level_view.name_list [5] AS "五级组织单元名称", dep_level_view.name_list [6] AS "六级组织单元名称", dep_level_view.name_list [7] AS "七级组织单元名称", tran_dep.value AS department,-- 部门 tran_position.value AS position_name, – 岗位 tran_job_level.value AS job_level, job_classification.name AS job_classification, – 职务 – 职级 tran_status.value AS emp_status, – 员工状态 case emp_job.x_zgzt when '1' then '正常在岗' when '2' then '不在岗' when '3' then '借调' when '4' then '挂职' when '5' then '派出（借调）' when '6' then '派出（挂职）' when '7' then '双兼' else '' end as x_zgzt, – 在岗状态 – 首次工作日期 emp.hire_date, – 入职日期 emp.termination_date, – 离职日期 pay_grade.name AS pay_grade, – 薪等 hpsv.name as hpsv_name --薪资方案 – 雇佣信息 FROM emp_job LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id LEFT JOIN hr_department AS dep ON emp_job.department_id = dep.eroad_index_id AND dep.eroad_index_id IS NOT NULL AND dep.eroad_start_date <= current_date AND (dep.eroad_end_date >= current_date OR dep.eroad_end_date IS NULL) AND dep.active = TRUE LEFT join ir_translation AS tran_dep ON tran_dep.res_id = dep.id AND tran_dep.lang='zh_CN' AND tran_dep.name='hr.department,name' LEFT JOIN hr_position AS position ON emp_job.position_id = position.eroad_index_id AND position.eroad_index_id IS NOT NULL AND position.eroad_start_date <= current_date AND (position.eroad_end_date >= current_date OR position.eroad_end_date IS NULL) AND position.active = TRUE LEFT join ir_translation AS tran_position ON tran_position.res_id = position.id AND tran_position.lang='zh_CN' AND tran_position.name='hr.position,name' LEFT JOIN employee_status AS emp_status ON emp_job.employee_status = emp_status.id LEFT join ir_translation AS tran_status ON tran_status.res_id = emp_status.id AND tran_status.lang='zh_CN' AND tran_status.name='employee.status,name' LEFT JOIN employee_type AS emp_type ON emp_job.employee_type = emp_type.id LEFT join ir_translation AS tran_emp_type ON tran_emp_type.res_id = emp_type.id AND tran_emp_type.lang='zh_CN' AND tran_emp_type.name='employee.type,name' LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id AND job_level.active = TRUE LEFT join ir_translation AS tran_job_level ON tran_job_level.res_id = job_level.id AND tran_job_level.lang='zh_CN' AND tran_job_level.name='job.level,name' – 自然人 LEFT JOIN per_person AS person ON emp.person_id = person.id left join per_national_id as per_nati on per_nati.person_id=person.id and per_nati.is_primary = TRUE and per_nati.card_type=22 – 证件类型 LEFT JOIN res_selection AS card_type ON per_nati.card_type = card_type.id LEFT join ir_translation AS tran_card ON tran_card.res_id = card_type.id AND tran_card.lang='zh_CN' AND tran_card.name='res.selection,name' – 主银行卡信息 LEFT JOIN (SELECT person_id, max(id) AS information_id FROM person_bank_information WHERE person_id IS NOT NULL and use=43 GROUP BY person_id) AS bank_rel ON person.id = bank_rel.person_id LEFT JOIN person_bank_information AS bank_information ON bank_rel.information_id = bank_information.id – 副银行卡信息 LEFT JOIN (SELECT person_id, max(id) AS information_id FROM person_bank_information WHERE person_id IS NOT NULL and use=44 GROUP BY person_id) AS bank_rel1 ON person.id = bank_rel1.person_id LEFT JOIN person_bank_information AS bank_information1 ON bank_rel1.information_id = bank_information1.id LEFT JOIN res_country AS country_of_bank ON bank_information.country_of_bank = country_of_bank.id LEFT JOIN res_selection AS bank_identification_number_type ON bank_information.bank_identification_number_type = bank_identification_number_type.id LEFT JOIN res_selection AS bank_use ON bank_information.use = bank_use.id – 联系人信息 LEFT JOIN (SELECT person_id, max(id) AS emergency_contact_id FROM emergency_contact WHERE person_id IS NOT NULL GROUP BY person_id) AS emergency_contact_rel ON person.id = emergency_contact_rel.person_id LEFT JOIN emergency_contact AS emergency_contact ON emergency_contact_rel.emergency_contact_id = emergency_contact.id LEFT JOIN res_selection AS emergency_contact_relationship ON emergency_contact.relationship = emergency_contact_relationship.id left join ir_translation AS tran_relationship ON tran_relationship.res_id = emergency_contact_relationship.id AND tran_relationship.lang='zh_CN' AND tran_relationship.name='res.selection,name' – 个人信息 LEFT JOIN per_personal AS personal ON person.per_personal = personal.eroad_index_id AND personal.eroad_index_id IS NOT NULL AND personal.eroad_start_date <= current_date AND (personal.eroad_end_date >= current_date OR personal.eroad_end_date IS NULL) AND personal.active = TRUE --职务 LEFT JOIN job_classification AS job_classification ON emp_job.job_classification_id = job_classification.eroad_index_id AND job_classification.eroad_index_id IS NOT NULL AND job_classification.eroad_start_date <= current_date AND (job_classification.eroad_end_date >= current_date OR job_classification.eroad_end_date IS NULL) AND job_classification.active = TRUE LEFT JOIN (SELECT employee_id, max(id) AS solution_id FROM hr_payroll_solution where start_date <= current_date and (end_date is null or end_date >= current_date) GROUP BY employee_id) AS solution_rel ON emp.id = solution_rel.employee_id LEFT JOIN hr_payroll_solution on hr_payroll_solution.id = solution_rel.solution_id LEFT JOIN hr_payroll_solution_view hpsv on hpsv.id = hr_payroll_solution.solution_id LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id LEFT JOIN pay_grade AS pay_grade on emp_job.pay_grade = pay_grade.id AND pay_grade.active = TRUE WHERE 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) aa