sql如下： select distinct * from ( WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, name, code, ARRAY [name] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle, x_jigoucategory from hr_department 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, dep_table.name, dep_table.code, dep_level_view.name_list || dep_table.name 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 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 1 emp_count, emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.x_number, – 7位员工编号 emp.username, – UASS emp.last_name, – 姓 emp.first_name, --名 emp.name, – 员工姓名 personal.x_namepinyin, – 姓名拼音 personal.nickname, – 英文名 CASE selection_gender.name WHEN 'Female' THEN '女' WHEN 'Male' THEN '男' else '' END gender, – 性别 person.age, – 年龄 res_country_state.name birth_province, – 出生地省 res_city.description birth_city, --出生地市 person.date_of_birth,-- 出生日期 emp.inner_working_age,-- 公龄 emp.email, – 邮箱 emp.business_phone, – 电话 emp.hire_date, – 入职日期 emp.original_start_date, – 参加工作时间 emp.first_date_worked, – 来建行时间 emp.social_working_age, – 当前工龄 emp.x_onboardbeforeworkyear, – 入职前工龄 probation_month_selection.name x_probationmonths, – 试用期月份 emp.probation_period_end_date, – 试用期结束日期 emp.probation_date, – 转正日期 CASE emp.is_probation WHEN 'f' THEN '否' WHEN 'f' THEN '是' ELSE '' END is_probation, --是否试用期 emp.termination_date, – 离职日期 CASE selection_marital.name WHEN 'Unmarried' THEN '未婚' when 'Married' then '已婚' ELSE selection_marital.name END marital_status, – 婚姻状况 translation_guoji.value nationality_id, – 国籍 res_nation.name nation, – 民族 political_status.name political_status, – 政治面貌 personal.x_communistparty, – 入党日期 jiguan_state.name jiguan_province, – 籍贯省 jiguan_city.description jiguan_city, – 籍贯市 education_selection.name higest_education, – 最高学历 personal.x_major, – 最高学历所学专业 personal.x_school, – 最高学历毕业院校 hujistate.name huji_state, – 户籍所在省 hujicity.description huji_city, – 户籍所在市 CASE personal.x_shshebao WHEN 't' THEN '是' WHEN 'f' THEN '否' ELSE '' END x_shshebao, – 有无社保账户 CASE personal.x_shgongjingji WHEN 't' THEN '是' WHEN 'f' THEN '否' ELSE '' END x_shgongjingji, – 有无基本公积金账户 personal.x_bcgjjaccount, – 补充公积金账户 personal.x_gjjaccount, – 基础公积金账户 CASE personal.x_is_hexinrencai WHEN 't' THEN '是' WHEN 'f' THEN '否' ELSE '' END x_is_hexinrencai, – 是否核心人才 CASE personal.x_is_abcdmix WHEN 't' THEN '是' WHEN 'f' THEN '否' ELSE '' END x_is_abcdmix, – 是否ABCDMIX hukouxingzhi_selection.name x_hukouxingzhi, – 户口性质 hukoustate.name hukoustate, – 户口所在省 hukoucity.description hukoucity, --户口所在市 personal.x_hukoulocation, – 户口详细地址 personal.current_residence, – 家庭住址精确到门牌号 per_national_id.national_id, – 身份证号码 person_bank_information.bank_name, – 开户银行 person_bank_information.bank_account, – 银行账号 legal_entity.name legal_entity_id, – 签约单位 x_jigoucategory_selection.name x_jigoucategory, – 机构分类 hr_department.code department_code, – 部门编码 array_to_string(hr_department.name_list,'/') department_name, – 部门名称 hr_position.code position_code, – 岗位编码 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, – 员工状态 job_classification.name job_classification_name, – 团队职务 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, – 增员类别 x_nengji_selection.name x_nengji, – 能级 job_level.name job_level_name, – 职级 x_jianhanghuazhuanleixing_selection.name x_jianhanghuazhuanleixing, – 建行划转类型 x_parent_sequence.name x_parent_sequence, – 一级序列 x_secondjobsequence_sequence.name x_secondjobsequence, – 二级序列 x_tongdao_selection.name x_tongdao_name, – 通道 job_grade.name job_grade_name, – 行内职等 hr_location.name hr_location_name, – 办公所在大楼 x_bgchangyongdi_selection.name x_bgchangyongdi, – 办公常用地 x_buildlocate_selection.name x_buildlocate, – 办公所在大楼 x_contractlocation_state.name x_contractlocation, – 合同履约地省 x_hetonglvyuedicity.description x_hetonglvyuedicity, – 合同履约地市 x_companylocate.name x_companylocate, – 办公常驻地省 x_worklocation.description x_worklocation, – 办公常住地市 x_financelocate.name x_financelocate, – 财务报销地省 x_financelocation.description x_financelocation, – 财务报销地市 x_socialsecurity.name x_socialsecurity, – 社保报销地省 x_societylocaitoncity.description x_societylocaitoncity, – 社保报销地市 x_salaryagency.name x_salaryagency, – 发薪机构 emp.x_hrbp_temp, – 工作伙伴 emp_job.eroad_start_date, emp_job.eroad_end_date, 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级 full_time_edu.start_date full_start_date, – 全日制入学日期, full_time_edu.end_date full_end_date, – 全日制毕业日期, full_time_edu.school full_school, – 全日制学校 full_time_background_selecton.name full_background, – -- 全日制学历 part_time_edu.start_date part_start_date, – 在职入学日期, part_time_edu.end_date part_end_date, – 在职毕业日期, part_time_edu.school part_school, – 在职学校 part_time_background_selecton.name part_background, – -- 在职学历 person_work_experience.start_date pre_job_start_date, – 上份工作起始日期 person_work_experience.end_date pre_job_end_date, – 上份工作终止日期 person_work_experience.company pre_job_company, – 上份工作所在单位 person_work_experience.job pre_job_job, – 上份工作职务岗位 person_work_experience.department pre_job_department, – 上份工作部门 person_work_experience.certifier pre_job_certifier, – 上份工作证明人 person_work_experience.certifier_phone pre_job_certifier_phone, – 上份工作证明人联系方式 person_work_experience.x_zhbianma pre_job_x_zhbianma, – 上份工作总行编码 person_work_experience.x_job pre_job_x_job, – 上份工作从事工作 case when person_work_experience.x_now=True then '是' when person_work_experience.x_now=False then '否' else '' end pre_job_x_now – 上份工作是否当前 from hr_employee emp left join per_person person on emp.person_id = person.id left join per_personal personal on person.per_personal = personal.id left join res_selection selection_gender on personal.gender = selection_gender.id left join res_country_state on person.x_birthprovince = res_country_state.id left join res_city on person.x_birthcity = res_city.id left join res_selection selection_marital on personal.marital_status = selection_marital.id left join res_country on personal.nationality_id = res_country.id left join ir_translation translation_guoji on res_country.name=translation_guoji.src and translation_guoji.lang='zh_CN' and translation_guoji.name ='res.country,name' and translation_guoji.res_id=res_country.id left join res_nation on personal.nation = res_nation.id left join political_status on personal.political_status = political_status.id left join res_country_state jiguan_state on personal.x_jiguan = jiguan_state.id left join res_city jiguan_city on personal.x_jiguancity = jiguan_city.id left join res_selection education_selection on personal.x_heightesteducation = education_selection.id left join res_country_state hujistate on personal.state_id = hujistate.id left join res_city hujicity on personal.city_id = hujicity.id left join res_selection hukouxingzhi_selection on personal.x_hkxingzhi = hukouxingzhi_selection.id left join res_country_state hukoustate on personal.x_hukouprovince = hukoustate.id left join res_city hukoucity on personal.x_hukoucity = hukoucity.id left join per_national_id on person.id = per_national_id.person_id and per_national_id.is_primary = True LEFT JOIN (SELECT person_id, max(id) AS information_id FROM person_bank_information WHERE person_id IS NOT NULL GROUP BY person_id) AS bank_rel ON person.id = bank_rel.person_id left join person_bank_information on bank_rel.information_id = person_bank_information.id 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 left join legal_entity on emp_job.legal_entity_id = legal_entity.id LEFT JOIN dep_level_view hr_department ON hr_department.cid = emp_job.department_id left join res_selection x_jigoucategory_selection on hr_department.x_jigoucategory = x_jigoucategory_selection.id 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 job_classification on emp_job.job_classification_id = job_classification.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' and employee_type_translation.res_id=employee_type.id 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 res_selection x_nengji_selection on emp_job.x_nengji = x_nengji_selection.id left join job_level on emp_job.job_level = job_level.id left join res_selection x_jianhanghuazhuanleixing_selection on emp_job.x_jianhanghuazhuanleixing = x_jianhanghuazhuanleixing_selection.id left join job_sequence x_parent_sequence on emp_job.x_parent_sequence_id = x_parent_sequence.id left join job_sequence x_secondjobsequence_sequence on emp_job.x_secondjobsequence = x_secondjobsequence_sequence.id left join res_selection x_tongdao_selection on emp_job.x_tongdao = x_tongdao_selection.id left join job_grade on emp_job.job_grade_id = job_grade.id left join hr_location on emp_job.location_id = hr_location.id left join res_selection x_bgchangyongdi_selection on emp_job.x_bgchangyongdi = x_bgchangyongdi_selection.id left join res_selection x_buildlocate_selection on emp_job.x_buildlocate = x_buildlocate_selection.id left join res_country_state x_contractlocation_state on emp_job.x_contractlocation = x_contractlocation_state.id left join res_city x_hetonglvyuedicity on emp_job.x_hetonglvyuedicity = x_hetonglvyuedicity.id left join res_country_state x_companylocate on emp_job.x_companylocate = x_companylocate.id left join res_city x_worklocation on emp_job.x_worklocation = x_worklocation.id left join res_country_state x_financelocate on emp_job.x_financelocate = x_financelocate.id left join res_city x_financelocation on emp_job.x_financelocation = x_financelocation.id left join res_country_state x_socialsecurity on emp_job.x_socialsecurity = x_socialsecurity.id left join res_city x_societylocaitoncity on emp_job.x_societylocaitoncity = x_societylocaitoncity.id left join res_selection x_salaryagency on emp_job.x_salaryagency = x_salaryagency.id left join res_selection probation_month_selection on emp.x_probationmonth=probation_month_selection.id left join (select * from person_education full_time_edu left join res_selection ftxxxs_selection on full_time_edu.x_learnform=ftxxxs_selection.id left join res_selection full_time_background_selecton on full_time_edu.educational_background=full_time_background_selecton.id where ftxxxs_selection.code='education_type_quanrizhi' and full_time_edu.x_heightest=True and full_time_background_selecton.code not in ('education.31','education.12','education.10','education.23','education.14')) full_time_edu on person.id=full_time_edu.person_id left join res_selection ftxxxs_selection on full_time_edu.x_learnform=ftxxxs_selection.id and ftxxxs_selection.code='education_type_quanrizhi' and full_time_edu.x_heightest=True left join res_selection full_time_background_selecton on full_time_edu.educational_background=full_time_background_selecton.id and full_time_background_selecton.code not in ('education.31','education.12','education.10','education.23','education.14') left join (select * from person_education full_time_edu left join res_selection ftxxxs_selection on full_time_edu.x_learnform=ftxxxs_selection.id left join res_selection full_time_background_selecton on full_time_edu.educational_background=full_time_background_selecton.id where ftxxxs_selection.code='education_type_zaizhi' and full_time_edu.x_heightest=True and full_time_background_selecton.code not in ('education.31','education.12','education.10','education.23','education.14')) part_time_edu on person.id=part_time_edu.person_id left join res_selection ptxxxs_selection on part_time_edu.x_learnform=ptxxxs_selection.id and ptxxxs_selection.code='education_type_zaizhi' and part_time_edu.x_heightest=True left join res_selection part_time_background_selecton on part_time_edu.educational_background=part_time_background_selecton.id and part_time_background_selecton.code not in ('education.31','education.12','education.10','education.23','education.14') left join (select per_person.id the_person_id, max(person_work_experience.start_date) the_start_date from hr_employee left join per_person on hr_employee.person_id=per_person.id left join person_work_experience on per_person.id=person_work_experience.person_id where person_work_experience.start_date<hr_employee.hire_date group by per_person.id) person_work_experience_mid on person.id=person_work_experience_mid.the_person_id left join person_work_experience on person_work_experience.person_id=person_work_experience_mid.the_person_id and person_work_experience.start_date=person_work_experience_mid.the_start_date where – per_national_id.is_primary = True or per_national_id.person_id is null 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 and emp.active=True) tall