sql如下： 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 '女' ELSE '男' END gender, – 性别 person.age, – 年龄 res_country_state.name birth_province, – 出生地省 res_city.name 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 '否' ELSE '是' END is_probation, --是否试用期 emp.termination_date, – 离职日期 CASE selection_marital.name WHEN 'Unmarried' THEN '未婚' ELSE '已婚' 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.name jiguan_city, – 籍贯市 education_selection.name higest_education, – 最高学历 personal.x_major, – 最高学历所学专业 personal.x_school, – 最高学历毕业院校 hujistate.name huji_state, – 户籍所在省 hujicity.name huji_city, – 户籍所在市 CASE personal.x_shshebao WHEN 't' THEN '是' ELSE '否' END x_shshebao, – 有无社保账户 CASE personal.x_shgongjingji WHEN 't' THEN '是' ELSE '否' END x_shgongjingji, – 有无基本公积金账户 personal.x_bcgjjaccount, – 补充公积金账户 personal.x_gjjaccount, – 基础公积金账户 CASE personal.x_is_hexinrencai WHEN 'f' THEN '否' ELSE '是' END x_is_hexinrencai, – 是否核心人才 CASE personal.x_is_abcdmix WHEN 'f' THEN '否' ELSE '是' END x_is_abcdmix, – 是否ABCDMIX hukouxingzhi_selection.name x_hukouxingzhi, – 户口性质 hukoustate.name hukoustate, – 户口所在省 hukoucity.name 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, – 部门编码 hr_department.name department_name, – 部门名称 hr_position.code position_code, – 岗位编码 hr_position.name position_name, – 岗位 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' 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.name x_hetonglvyuedicity, – 合同履约地市 x_companylocate.name x_companylocate, – 办公常驻地省 x_worklocation.name x_worklocation, – 办公常住地市 x_financelocate.name x_financelocate, – 财务报销地省 x_financelocation.name x_financelocation, – 财务报销地市 x_socialsecurity.name x_socialsecurity, – 社保报销地省 x_societylocaitoncity.name x_societylocaitoncity, – 社保报销地市 x_salaryagency.name x_salaryagency, – 发薪机构 emp.x_hrbp_temp, – 工作伙伴 0 x_probationmonth – 废弃 from hr_employee emp left join res_selection selection_gender on emp.gender = selection_gender.id 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_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' 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 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 left join emp_job on emp.job_info_id = emp_job.id left join legal_entity on emp_job.legal_entity_id = legal_entity.id left join hr_department on emp_job.department_id = hr_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' 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 where per_national_id.is_primary = True or per_national_id.person_id is 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