三张报表的sql分别如下： ========= select emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.x_number, – 7位员工编号 emp.username, – UASS emp.name, – 员工姓名 emp.hire_date, – 入职日期 hr_department.code department_code, – 部门编码 hr_department.name department_name, – 部门名称 hr_position.name position_name, – 岗位 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' ELSE '预入职' END employee_status, – 员工状态 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, – 增员类别 emp.x_hrbp_temp, – 工作伙伴 person_work_experience.start_date, – 开始日期 person_work_experience.end_date, – 终止日期 person_work_experience.company, – 所在单位 person_work_experience.job, – 岗位 person_work_experience.department, – 部门 person_work_experience.certifier,-- 证明人 person_work_experience.certifier_phone, – 证明人联系方式 person_work_experience.x_zhbianma,-- 总行编码 person_work_experience.x_job,-- 从事工作 CASE person_work_experience.x_now WHEN 'f' THEN '否' ELSE '是' END – 是否当前 from hr_employee emp 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 hr_department on emp_job.department_id = hr_department.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 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' 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 per_person person on emp.person_id = person.id left join person_work_experience on person_work_experience.person_id=person.id where emp.active=True ======================= select emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.x_number, – 7位员工编号 emp.username, – UASS emp.name, – 员工姓名 emp.hire_date, – 入职日期 hr_department.code department_code, – 部门编码 hr_department.name department_name, – 部门名称 hr_position.name position_name, – 岗位 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' ELSE '预入职' END employee_status, – 员工状态 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, – 增员类别 emp.x_hrbp_temp, – 工作伙伴 CASE person_education.x_heightest WHEN 'f' THEN '否' when 't' THEN '是' END x_heightest,-- 是否最高学历 CASE person_education.x_quanrizhi WHEN 'f' THEN '否' when 't' THEN '是' END x_quanrizhi,-- 是否全日制 CASE person_education.x_is_first_education WHEN 'f' THEN '否' when 't' THEN '是' else '否' END x_is_first_education,-- 是否第一学历 x_is_height_selection.name x_is_height,-- 是否最高学历 person_education.end_date,-- 毕业日期 person_education.school,-- 学校名称 person_education.start_date,-- 入职日期 case degree_selection.name when 'Ph.D.' then '博士学位' when 'Bachelor' then '学士学位' when 'Master' then '硕士学位' when 'Others' then '其他' end academic_degree,-- 学位 person_education.profession profession,-- 专业 person_education.x_year,-- 学制 school_type_selection.name x_schoolform,-- 学校性质 x_learnform_selection.name x_learnform,-- 学习形式 person_education.x_zhbianma, – 总行编码 background_selection.name educational_background – 学历 from hr_employee emp 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 hr_department on emp_job.department_id = hr_department.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 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' 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 per_person person on emp.person_id = person.id left join person_education on person_education.person_id = person.id left join res_selection degree_selection on person_education.academic_degree=degree_selection.id left join res_selection x_is_height_selection on person_education.x_is_height=x_is_height_selection.id left join res_selection school_type_selection on person_education.x_schoolform=school_type_selection.id left join res_selection x_learnform_selection on person_education.x_learnform=x_learnform_selection.id left join res_selection background_selection on person_education.educational_background=background_selection.id where emp.active=True ====================== select emp.employee_number, – 8位员工工号 emp.x_15number, – 15位员工工号 emp.x_number, – 7位员工编号 emp.username, – UASS emp.name, – 员工姓名 emp.hire_date, – 入职日期 hr_department.code department_code, – 部门编码 hr_department.name department_name, – 部门名称 hr_position.name position_name, – 岗位 CASE employee_status.name WHEN 'Active' THEN '在职' when 'Inactive' THEN '离职' ELSE '预入职' END employee_status, – 员工状态 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, – 增员类别 emp.x_hrbp_temp, – 工作伙伴 relationship_type_selection.name relationship_type, – 关系 per_person_relationship.first_name, – 家属姓名 CASE gender_selection.name WHEN 'Male' THEN '男' when 'Female' THEN '女' END gender,-- 性别 per_person_relationship.x_birthdate,-- 出生日期 per_person_relationship.x_shenfenzheng,-- 身份证号 edu_selection.name x_education,-- 文化程度 political_status.name x_political,-- 政治面貌 per_person_relationship.x_work,-- 工作单位 CASE per_person_relationship.x_jhyuangong WHEN 'f' THEN '否' when 't' THEN '是' END x_jhyuangong,-- 是否建行员工 per_person_relationship.x_job,-- 职务 per_person_relationship.x_jianhangemployeeid,-- 建行员工编号 CASE per_person_relationship.x_is_agree WHEN 'f' THEN '否' when 't' THEN '是' END x_is_agree,-- 声明确认 per_person_relationship.x_zhbianma – 总行编码 from hr_employee emp 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 hr_department on emp_job.department_id = hr_department.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 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' 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 per_person person on emp.person_id = person.id left join per_person_relationship on per_person_relationship.person_id=person.id left join res_selection relationship_type_selection on relationship_type_selection.id=per_person_relationship.relationship_type left join res_selection gender_selection on per_person_relationship.x_gender=gender_selection.id left join political_status on per_person_relationship.x_political=political_status.id left join res_selection edu_selection on edu_selection.id=per_person_relationship.x_education where emp.active=True