代码： WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, dep_trans.value,code, ARRAY [dep_trans.value] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' 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_transt.value, dep_table.code, dep_level_view.name_list || dep_transt.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 dep_transt on dep_transt.res_id=dep_table.id and dep_transt.name ='hr.department,name' and dep_transt.lang='zh_CN' 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 ), emp_category AS ( – 员工标签 SELECT emp_job_id AS emp_id, ARRAY_AGG(category.name) AS categories FROM category_hr_employee_rel LEFT JOIN hr_employee_category AS category ON category_hr_employee_rel.category_id = category.id GROUP BY emp_job_id ) SELECT – 基本信息 emp.id, – 员工id emp.employee_number, – 员工工号 case when emp_job.eroad_start_date>personal.eroad_start_date or personal.eroad_start_date is null then emp_job.eroad_start_date else personal.eroad_start_date end as effective_date, case when emp_job.eroad_end_date<personal.eroad_end_date or personal.eroad_end_date is null then emp_job.eroad_end_date else personal.eroad_end_date end as effectoff_date, emp_job.eroad_start_date, emp_job.eroad_end_date, personal.eroad_start_date as per_start_date, personal.eroad_end_date as per_end_date, (case when dl.name_list is null then '' else (case when dl.name_list@>array ['NTL'] then 'NTL' else 'NCH' end) end) as NCHNTL, emp.name AS emp_name, – 员工姓名 tran_emp_status.value AS emp_status, – 员工状态 emp_category.categories AS emp_categories, – 员工标签 tran_gender.value AS emp_gender, – 员工性别 tran_emp_type.value AS emp_type, – 员工类型 emp.hire_date, – 入职日期 emp.first_date_worked, – 集团入职日期 emp.probation_date, – 转正日期 emp.termination_date, – 离职日期 emp.inner_working_age, – 公司服务年限 emp.original_start_date, – 社会工作开始日期 emp.social_working_age, – 社会服务年限 emp.probation_period_end_date, – 试用期结束日期2 legal_entity.name AS legal_entity_name, – 法人实体 bu.name AS bu_name, – 业务单元 division.name AS divison_name, – 分部 dep.name AS dep_name, – 部门 cost_center.name AS cost_center_name, – 成本中心 location.name AS clocation_name, – 地点 emp_country.name AS country_name, – 国家 position.name AS position_name, – 岗位 emp_job.position_entry_date, – 岗位入职日期 emp_group.name AS employee_group, – 员工分组 emp_manager.name AS manager_employee, – 上级 job_classification.name AS job_classification, – 职务 job_sequence.name AS job_sequence, – 职务序列 job_function.name AS job_function, – 职能 job_grade.name AS job_grade, – 职等 job_level.name AS job_level, – 职级 pay_grade.name AS pay_grade, – 薪等 pay_level.name AS pay_level, – 薪级 emp.x_hireyearmonth AS "入职年月数", --入职年月数 emp.x_neibugongling AS "内部工龄计算日期", --内部工龄计算日期 tran_recruitment_students.value AS x_recruitment_students, – 应届生采用 tran_x_language.value AS x_language, – 语言能力 tran_x_language_certificate_level.value AS x_language_certificate_level, – 语言级别 tran_x_dispatch_company.value AS x_dispatch_company, – 所属派遣公司 tran_x_agency_company.value AS x_agency_company, – 所属代理公司 tran_x_employment_location.value AS x_employment_location, – 录用地 tran_x_working_content.value AS x_working_content, – 工作内容 tran_x_organization_type.value AS x_organization_type, – 组织类别 dep.x_dept_bu AS "部", – 部 dep.x_dept_zu AS "组", – 组 dep.x_dept_ke AS "课", – 课 emp.x_external_woking_age AS "外部工龄_总月数", CASE emp.is_probation WHEN 'f' THEN '否' ELSE '是' END is_probation, --是否试用期 emp.is_probation AS “是否在试用期”, benefits_company_rule.name AS social_name, --社保缴纳城市 – 个人信息 personal.marital_status, – 婚姻状况 marital_status_trans.value marital_status_name, – 婚姻状况 personal.registered_residence_type, – 户口类型 country.name AS country, – 国籍 nation.name AS nation, – 民族 country_state.name AS country_state, – 籍贯 political.name AS political, – 政治面貌 children_situation.name AS children_situation, – 生育状况 personal.x_pinyin AS “拼音”, – 拼音 personal.x_preson_phone AS "个人移动电话号码", personal.x_company_phone AS "公司手机号码", personal.x_wechat AS "微信号", personal.registered_residence_address AS "户籍所在地", personal.current_residence AS "现居地址", personal.x_taxpayer_id AS "纳税人识别号", – 自然人信息 person.date_of_birth, – 出生日期 person.place_of_birth, – 出生地 tran_card_type.value AS card_type, – 证件类型 person.national_id AS card_national_id, – 证件号码 person.due_date AS card_due_date, – 证件到期日 person.age, – 年龄 person.zodiac_sign, – 星座 person.zodiac, – 生肖 – 邮箱信息 per_email.email_address, – 邮箱地址 email_type.name AS email_type, – 邮箱类型 – 电话信息 per_phone.phone_number, – 电话 phone_type.name AS phone_type, – 电话类型 huji_type_trans.value huji_type, – 户籍性质 sheng_state.name huji_sheng, – 户籍所在地-省 shi_city.name huji_shi, – 户籍所在地-市 – 银行卡信息 country_of_bank.name AS children_situation, – 银行所在国 bank_identification_number_type.name AS bank_identification_number_type, – 银行卡类型 bank_information.bank_name, – 银行名称 bank_information.bank_identification_number, – 金融机构识别码 bank_information.bank_address, – 银行地址 bank_information.bank_account, – 银行账户 bank_use.name AS bank_use, – 用途 bank_information.bank_account_holder, – 开户人姓名 – 学历信息 person_education.start_date AS edu_start_date, – 开始日期 person_education.end_date AS edu_end_date, – 结束日期 edu_method.name AS edu_method, – 教育方式 person_education.school AS edu_school, – 学校 tran_edu_background.value AS edu_background, – 学历 tran_academic_degree.value AS academic_degree, – 学位 person_education.profession, – 专业 person_education.profession_type, – 专业类型 person_education.degree_certificate_number, – 学位证书号 – 工作信息 person_work.start_date AS work_start_date, – 开始日期 person_work.end_date AS work_end_date, – 结束日期 person_work.industry, – 行业 person_work.company, – 公司 person_work.department, – 部门 person_work.job, – 岗位 person_work.monthly_salary, – 月薪 person_work.reason_of_leaving, – 离职原因 person_work.certifier, – 证明人 certifier_relationship.name AS certifier_relationship, – 证明人关系 person_work.certifier_job, – 证明人岗位 person_work.certifier_phone, – 证明人号码 – 紧急联系人信息 emergency_contact.name AS emergency_contact_name, – 紧急联系人名称 emergency_contact.phone AS emergency_contact_phone, – 紧急联系人号码 emergency_contact_relationship.name AS emergency_contact_relationship, – 紧急联系人关系 contract_current.contract_date_start, – 试用期开始日期 contract_current.contract_date_end, – 合同结束日期 contract_current.trial_date_start, – 试用期开始日期 contract_current.trial_date_end, – 试用期结束日期 tran_working_hours.value AS "工时类型", tran_x_htqdcishu.value AS "劳动合同签订次数", – 劳动合同签订次数 tran_contract_type.value AS contract_type, – 合同类型 tran_contract_category.value AS category_type – 期限类型 ,'' as extra_column – 雇佣信息 FROM emp_job LEFT JOIN hr_employee emp ON emp_job.employee_id = emp.id LEFT JOIN emp_category ON emp_job.employee_id = emp_category.emp_id LEFT JOIN res_selection AS emp_gender ON emp.gender = emp_gender.id LEFT JOIN ir_translation AS tran_gender on tran_gender.res_id=emp_gender.id and tran_gender.lang='zh_CN' and tran_gender.name='res.selection,name' LEFT JOIN legal_entity AS legal_entity ON emp_job.legal_entity_id = legal_entity.eroad_index_id AND legal_entity.eroad_index_id IS NOT NULL AND legal_entity.eroad_start_date <= current_date AND (legal_entity.eroad_end_date >= current_date OR legal_entity.eroad_end_date IS NULL) AND legal_entity.active = TRUE LEFT JOIN hr_business_unit AS bu ON emp_job.business_unit_id = bu.eroad_index_id AND bu.eroad_index_id IS NOT NULL AND bu.eroad_start_date <= current_date AND (bu.eroad_end_date >= current_date OR bu.eroad_end_date IS NULL) AND bu.active = TRUE LEFT JOIN hr_division AS division ON emp_job.division_id = division.eroad_index_id AND division.eroad_index_id IS NOT NULL AND division.eroad_start_date <= current_date AND (division.eroad_end_date >= current_date OR division.eroad_end_date IS NULL) AND division.active = TRUE 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 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 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 cost_center ON emp_job.cost_center_id = cost_center.id AND cost_center.active = TRUE LEFT JOIN hr_location AS location on emp_job.location_id = location.id AND location.active = TRUE LEFT JOIN res_country AS emp_country on emp_job.country = emp_country.id AND emp_country.active = TRUE LEFT JOIN employee_status emp_status ON emp_job.employee_status = emp_status.id LEFT JOIN ir_translation AS tran_emp_status on tran_emp_status.res_id=emp_status.id and tran_emp_status.lang='zh_CN' and tran_emp_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 employee_group AS emp_group on emp_job.employee_group = emp_group.id AND emp_group.active = TRUE LEFT JOIN hr_employee AS emp_manager on emp_job.manager_employee_id = emp_manager.id AND emp_manager.active = TRUE LEFT JOIN job_sequence AS job_sequence on emp_job.job_sequence_id = job_sequence.id AND job_sequence.active = TRUE LEFT JOIN job_function AS job_function on emp_job.job_function_id = job_function.id AND job_function.active = TRUE LEFT JOIN job_grade AS job_grade on emp_job.job_grade_id = job_grade.id AND job_grade.active = TRUE LEFT JOIN job_level AS job_level on emp_job.job_level = job_level.id AND job_level.active = TRUE LEFT JOIN pay_grade AS pay_grade on emp_job.pay_grade = pay_grade.id AND pay_grade.active = TRUE LEFT JOIN pay_level AS pay_level on emp_job.pay_level = pay_level.id LEFT JOIN res_selection x_recruitment_students ON emp.x_recruitment_students=x_recruitment_students.id LEFT JOIN ir_translation AS tran_recruitment_students on tran_recruitment_students.res_id=x_recruitment_students.id and tran_recruitment_students.lang='zh_CN' and tran_recruitment_students.name='res.selection,name' LEFT JOIN res_selection x_language ON emp.x_language=x_language.id LEFT JOIN ir_translation AS tran_x_language on tran_x_language.res_id=x_language.id and tran_x_language.lang='zh_CN' and tran_x_language.name='res.selection,name' LEFT JOIN res_selection x_language_certificate_level ON emp.x_language_certificate_level=x_language_certificate_level.id LEFT JOIN ir_translation AS tran_x_language_certificate_level on tran_x_language_certificate_level.res_id=x_language_certificate_level.id and tran_x_language_certificate_level.lang='zh_CN' and tran_x_language_certificate_level.name='res.selection,name' LEFT JOIN res_selection x_dispatch_company ON emp_job.x_dispatch_company=x_dispatch_company.id LEFT JOIN ir_translation AS tran_x_dispatch_company on tran_x_dispatch_company.res_id=x_dispatch_company.id and tran_x_dispatch_company.lang='zh_CN' and tran_x_dispatch_company.name='res.selection,name' LEFT JOIN res_selection x_agency_company ON emp_job.x_agency_company=x_agency_company.id LEFT JOIN ir_translation AS tran_x_agency_company on tran_x_agency_company.res_id=x_agency_company.id and tran_x_agency_company.lang='zh_CN' and tran_x_agency_company.name='res.selection,name' LEFT JOIN hr_location x_employment_location ON emp_job.x_employment_location=x_employment_location.id LEFT JOIN ir_translation AS tran_x_employment_location on tran_x_employment_location.res_id=x_employment_location.id and tran_x_employment_location.lang='zh_CN' and tran_x_employment_location.name='hr.location,name' LEFT JOIN res_selection x_working_content ON emp_job.x_working_content=x_working_content.id LEFT JOIN ir_translation AS tran_x_working_content on tran_x_working_content.res_id=x_working_content.id and tran_x_working_content.lang='zh_CN' and tran_x_working_content.name='res.selection,name' LEFT JOIN res_selection x_organization_type ON emp_job.x_organization_type=x_organization_type.id LEFT JOIN ir_translation AS tran_x_organization_type on tran_x_organization_type.res_id=x_organization_type.id and tran_x_organization_type.lang='zh_CN' and tran_x_organization_type.name='organization.type,name' – 自然人 LEFT JOIN per_person AS person ON emp.person_id = person.id – 证件类型 LEFT JOIN res_selection AS card_type ON person.card_type = card_type.id – 邮箱信息 LEFT JOIN per_email AS per_email ON person.id = per_email.person_id AND per_email.is_primary = TRUE LEFT JOIN res_selection AS email_type ON per_email.email_type = email_type.id – 电话信息 LEFT JOIN per_phone AS per_phone ON person.id = per_phone.person_id AND per_phone.is_primary = TRUE LEFT JOIN res_selection AS phone_type ON per_phone.phone_type = phone_type.id – 银行卡信息 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 AS bank_information ON bank_rel.information_id = bank_information.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 person_education ON emp.person_id = person_education.person_id AND person_education.is_highest_education = TRUE LEFT JOIN res_selection AS edu_method ON person_education.study_method = edu_method.id LEFT JOIN res_selection AS edu_background ON person_education.educational_background = edu_background.id LEFT JOIN res_selection AS academic_degree ON person_education.academic_degree = academic_degree.id – 工作信息 LEFT JOIN (SELECT person_id, max(id) AS work_experience_id FROM person_work_experience WHERE person_id IS NOT NULL GROUP BY person_id) AS work_experience_rel ON person.id = work_experience_rel.person_id LEFT JOIN person_work_experience AS person_work ON work_experience_rel.work_experience_id = person_work.id LEFT JOIN res_selection AS certifier_relationship ON person_work.certifier_relationship = certifier_relationship.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 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>=emp_job.eroad_start_date and (personal.eroad_start_date<=emp_job.eroad_end_date or emp_job.eroad_end_date is null)) or (emp_job.eroad_start_date>=personal.eroad_start_date and (emp_job.eroad_start_date<=personal.eroad_end_date or personal.eroad_end_date is 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 res_country AS country ON personal.nationality_id = country.id LEFT JOIN res_nation AS nation ON personal.nation = nation.id LEFT JOIN res_country_state AS country_state ON personal.native_place = country_state.id LEFT JOIN political_status AS political ON personal.political_status = political.id LEFT JOIN res_selection AS children_situation ON personal.children_situation = children_situation.id LEFT JOIN benefits_employee_rule on benefits_employee_rule.employee_id=emp.id LEFT JOIN benefits_company_rule on benefits_employee_rule.social_insurance_rule_de_id = benefits_company_rule.id left join res_country_state sheng_state on personal.state_id=sheng_state.id left join res_selection huji_type_selection on personal.registered_residence_type=huji_type_selection.id left join ir_translation huji_type_trans on huji_type_trans.res_id=huji_type_selection.id and huji_type_trans.name ='res.selection,name' and huji_type_trans.lang='zh_CN' left join res_city shi_city on personal.city_id=shi_city.id left join res_selection marital_status_selection on personal.marital_status=marital_status_selection.id left join ir_translation marital_status_trans on marital_status_trans.res_id=marital_status_selection.id and marital_status_trans.name ='res.selection,name' and marital_status_trans.lang='zh_CN' left join (select max(contract_date_start) max_date, employee_id from hr_contract where x_bp_state='done' group by employee_id) mid_emp_contract on emp.id=mid_emp_contract.employee_id left join hr_contract as contract_current on contract_current.employee_id=emp.id and contract_current.contract_date_start=mid_emp_contract.max_date left join contract_agreement as contract_type on contract_type.id = contract_current.contract_agreement_id left join contract_category as contract_category on contract_category.id = contract_current.contract_category_id left join res_selection x_htqdcishu_id on contract_current.x_htqdcishu=x_htqdcishu_id.id left join ir_translation tran_x_htqdcishu on tran_x_htqdcishu.res_id=x_htqdcishu_id.id and tran_x_htqdcishu.lang='zh_CN' and tran_x_htqdcishu.name ='res.selection,name' left join res_selection working_hours_id on contract_current.working_hours=working_hours_id.id left join ir_translation tran_working_hours on tran_working_hours.res_id=working_hours_id.id and tran_working_hours.lang='zh_CN' and tran_working_hours.name ='res.selection,name' left join dep_level_view dl on dep.code=dl.code left join ir_translation tran_card_type on tran_card_type.res_id= card_type.id and tran_card_type.lang='zh_CN' and tran_card_type.name ='res.selection,name' left join ir_translation tran_contract_type on tran_contract_type.res_id= contract_type.id and tran_contract_type.lang='zh_CN' and tran_contract_type.name ='contract.agreement,name' left join ir_translation tran_contract_category on tran_contract_category.res_id= contract_category.id and tran_contract_category.lang='zh_CN' and tran_contract_category.name ='contract.agreement,name' left join ir_translation tran_edu_background on tran_edu_background.res_id= edu_background.id and tran_edu_background.lang='zh_CN' and tran_edu_background.name ='res.selection,name' left join ir_translation tran_academic_degree on tran_academic_degree.res_id= academic_degree.id and tran_academic_degree.lang='zh_CN' and tran_academic_degree.name ='res.selection,name' WHERE emp_job.eroad_index_id IS NOT NULL AND emp_job.status = 'active' AND emp_job.active = TRUE AND emp_job.employee_id IS NOT NULL;