花名册增加字段： 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 ), 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, – 员工工号 emp.name AS emp_name, – 员工姓名 case when emp_status.name='Active' then '在职' when emp_status.name='Inactive' then '离职' when emp_status.name='Pre-hire' then '预入职' when emp_status.name='Abandonment' then '放弃入职' else '' end AS emp_status, – 员工状态 emp_category.categories AS emp_categories, – 员工标签 case emp_gender.name when 'Male' then '男' when 'Female' then '女' else '' end as emp_gender, – 员工性别 case emp_type.name when 'Regular Employee' then '正式员工' when 'Temporary Employee' then '试用员工' when 'Trainee' then '实习生' when 'Outsourced Employee' then '外包员工' when 'Contractor' then '劳务派遣' when 'Part-Time Employee' then '兼职员工' when 'Others' then '其他' else '' end 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, – 社会服务年限 legal_entity.name AS legal_entity_name, – 法人实体 bu.name AS bu_name, – 业务单元 division.name AS divison_name, – 分部 dep_level_view.name_list [1] AS "一级部门", – 一级部门 – 一级组织单元名称 dep_level_view.name_list [2] AS "二级部门", – 二级部门 – 二级组织单元名称 dep_level_view.name_list [3] AS "三级部门", – 三级部门 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_job.x_gwdj,--岗位等级 emp_job.x_zydj,--专业等级 emp_job.x_ygxs,--用工形式 personal.current_residence as lxdz,--联系地址 emp_job.x_zplyfl,--招聘来源分类 emp_job.x_zpwz,--招聘网站 emp_job.x_nbtjr,--内部推荐人 emp_job.x_zpxx,--招聘学校 – 个人信息 case marital_status.name when 'Single' then '单身' when 'Unmarried' then '未婚' when 'Married' then '已婚' when 'Divorced' then '离异' when 'Widowed' then '丧偶' when 'Others' then '其他' else '' end as marital_status, – 婚姻状况 residence_type.name AS residence_name, – 户口类型 country.name AS country, – 国籍 nation.name AS nation, – 民族 country_state.name AS country_state, – 籍贯 political.name AS political, – 政治面貌 children_situation.name AS children_situation, – 生育状况 – 自然人信息 person.date_of_birth, – 出生日期 person.place_of_birth, – 出生地 card_type.name 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, – 电话类型 – 银行卡信息 country_of_bank.name AS bank_country, – 银行所在国 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, – 学校 case edu_background.name when 'Primary School' then '小学' when 'Secondary School' then '中学 'when 'Postgraduate' then '研究生' when 'High School' then '高中' when 'Vocational School' then '职业学校' when 'Ph.D.' then '博士学位' when 'Undergraduate' then '大学本科' when 'College' then '大专' else edu_background.name end as edu_background, – 学历 academic_degree.name 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, – 紧急联系人关系 – 销售合同信息 xsht.x_htbh as htbb, --合同编号 xsht.x_dyxm as dyxm, --对应项目 xsht.x_dyxs as dyxs, --对应销售 xsht.x_bz as bz, --备注 bcr_social.province_name, – 社保所在省份 bcr_social.city_name --社保所在城市 – 雇佣信息 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 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 employee_type AS emp_type ON emp_job.employee_type = emp_type.id 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 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_degree = 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 <= current_date AND (personal.eroad_end_date >= current_date OR personal.eroad_end_date IS NULL) AND personal.active = TRUE LEFT JOIN (SELECT x_per_person_id, max(id) AS x_per_id FROM x_builder_xsht WHERE x_per_person_id IS NOT NULL GROUP BY x_per_person_id) AS x_xsht ON person.id = x_xsht.x_per_person_id LEFT JOIN x_builder_xsht as xsht ON x_xsht.x_per_id = xsht.id 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 res_selection AS residence_type ON personal.registered_residence_type = residence_type.id LEFT JOIN res_selection AS marital_status ON personal.marital_status = marital_status.id left join benefits_employee_rule as ber on ber.employee_id=emp.id and ber.is_delete=0 and ber.effective_start_date <= current_date and ber.effective_end_date >= current_date left join benefits_company_rule as bcr_social on bcr_social.de_id = ber.social_insurance_rule_de_id and bcr_social.effective_start_date <= current_date and bcr_social.effective_end_date >= current_date and bcr_social.is_delete = 0 LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id 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 order by emp.employee_number