sql: 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') select position.code 岗位编码, coalesce(position_trans.value,position.name) 岗位名称, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, position.eroad_start_date 生效时间, position.eroad_end_date 实效时间, coalesce(hr_department_trans.value,hr_department.name) 所属部门, selection_json.selection_json->>hr_department.organization_level_id::varchar 部门级别, selection_json.selection_json->>hr_department.x_deptype::varchar 部门类型, p_position.name 上级岗位, case when position.status='active' then '有效' when position.status='inactive' then '实效' else '' end 是否有效, position.x_gwszmd 岗位设置目的, selection_json.selection_json->>position.position_criticality::varchar 是否关键岗位, case when position.x_sfmggw='yes' then '是' when position.x_sfmggw='no' then '否' else '' end 是否敏感岗位, x_builder_qzht.x_qzht 发展通道, x_builder_gwxl.x_gwxl 岗位序列, x_builder_zxl_copy.x_3pcj 三p层级, position.x_3pzj_x 三P职级, x_builder_3pzx.x_3pzx 三P职衔, position.x_3pzwmc 三P职务名称, x_builder_zj.x_zj 职级, selection_json.selection_json->>position.x_qzht::varchar 前中后台, zxl.x_zxl as 子序列, sijgw.x_4jgw as 四级岗位, wujgw.x_wjgw as 五级岗位, emp_count_sub.emp_count 在岗总人数 from hr_position as position left join selection_json on 1=1 left join ir_translation position_trans on position_trans.res_id=position.id and position_trans.name ='hr.position,name' and position_trans.lang='zh_CN' left join x_builder_gwxl on x_builder_gwxl.id=position.x_gwxl left join x_builder_zxl as zxl on zxl.id = position.x_zxl left join x_builder_4jgw as sijgw on sijgw.id = position.x_4jgw left join x_builder_wjgw as wujgw on wujgw.id = position.x_5jgw left join hr_business_unit on hr_business_unit.id=position.business_unit_id left join ir_translation bu_trans on bu_trans.res_id=hr_business_unit.id and bu_trans.name ='hr.business.unit,name' and bu_trans.lang='zh_CN' left join hr_department on position.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 p_position on position.parent_position_id=p_position.id left join x_builder_qzht on position.x_fztd=x_builder_qzht.id left join x_builder_zxl_copy on position.x_3pcj=x_builder_zxl_copy.id left join x_builder_3pzx on position.x_3pzx=x_builder_3pzx.id left join x_builder_zj on position.x_zj=x_builder_zj.id left join (select emp_job.position_id, count(distinct emp.id) emp_count from hr_employee emp left join emp_job on emp.job_info_id=emp_job.id where emp_job.employee_status=2 group by emp_job.position_id) emp_count_sub on emp_count_sub.position_id=position.eroad_index_id where position.eroad_index_id IS NOT NULL AND position.active = TRUE