月度转正人员信息表sql如下可以通过参数query_month设置筛选器: with selection_json as (SELECT jsonb_object_agg(res_id,value) selection_json from ir_translation where name='res.selection,name' and lang='zh_CN'), zhuanzheng_json as (select ' {"01":"提前转正","02":"按期转正","03":"延长试用期","04":"未通过试用期"} '::jsonb zhuanzheng_json) select emp.employee_number 员工工号, emp.name 姓名, selection_json.selection_json->>emp_job.x_czlx::varchar 操作类型, coalesce(employee_type_trans.value,employee_type.name) 员工类型, selection_json.selection_json->>emp_job.x_ywtx::varchar 业务条线, coalesce(bu_trans.value,bu.name) 所属业务单元, coalesce(hr_department_trans.value,hr_department.name) 部门, coalesce(hr_position_trans.value,hr_position.name) 岗位, selection_json.selection_json->>emp_job.x_qzht::varchar 前中后台, x_builder_gwxl.x_gwxl 岗位序列, x_builder_zxl.x_zxl 子序列, x_builder_4jgw.x_4jgw 四级岗位, x_builder_wjgw.x_wjgw 五级岗位, emp.hire_date 试用期开始日期, emp.probation_period_end_date 试用期结束日期, emp.probation_date 转正日期, zhuanzheng_json.zhuanzheng_json->>x_builder_syqk.x_syqjg::varchar 试用期结果 from hr_employee emp left join emp_job on emp_job.employee_id=emp.id left join selection_json on 1=1 left join employee_type on emp_job.employee_type=employee_type.id left join ir_translation employee_type_trans on employee_type_trans.res_id=employee_type.id and employee_type_trans.name ='employee.type,name' and employee_type_trans.lang='zh_CN' LEFT JOIN hr_business_unit AS bu ON emp_job.business_unit_id = bu.id left join ir_translation bu_trans on bu_trans.res_id=bu.id and bu_trans.name ='hr.business.unit,name' and bu_trans.lang='zh_CN' left join hr_department on emp_job.department_id=hr_department.id left join ir_translation hr_department_trans on hr_department_trans.res_id=hr_department.id and hr_department_trans.name ='hr.department,name' and hr_department_trans.lang='zh_CN' left join hr_position on emp_job.position_id=hr_position.id left join ir_translation hr_position_trans on hr_position_trans.res_id=hr_position.id and hr_position_trans.name ='hr.position,name' and hr_position_trans.lang='zh_CN' left join x_builder_zxl on emp_job.x_zxl=x_builder_zxl.id left join x_builder_4jgw on emp_job.x_4jgw=x_builder_4jgw.id left join x_builder_wjgw on emp_job.x_5jgw=x_builder_wjgw.id left join x_builder_syqk on x_builder_syqk.x_employee_id =emp.id left join x_builder_gwxl on emp_job.x_gwxl = x_builder_gwxl.id left join zhuanzheng_json on 1=1 where emp_job.eroad_index_id IS NOT NULL AND emp_job.eroad_start_date <= emp.probation_date AND (emp_job.eroad_end_date >= emp.probation_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 to_char(emp.probation_date,'yyyy-mm')='${query_month:2021-12}'