sql: WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, dep_trans.value,code, ARRAY [hr_department.code] as code_list, ARRAY [case when dep_trans.value is not null then dep_trans.value else hr_department.name end] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' 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, dep_transt.value, dep_table.code, dep_level_view.code_list || dep_table.code as code_list, dep_level_view.name_list || case when dep_transt.value is not null then dep_transt.value else dep_table.name end 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 dep_transt on dep_transt.res_id=dep_table.id and dep_transt.name ='hr.department,name' and dep_transt.lang='zh_CN' 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 ) select coalesce(hr_department_trans.value,hr_department.name) 部门, emp.employee_number 员工工号, emp.name 姓名, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, array_to_string(dep_level_view.name_list,'/') 部门全路径, x_builder_zj.x_zj 职级, coalesce(hr_overtime_type_trans.value,hr_overtime_type.name) 加班类型, to_char(hr_overtime_line.overtime_date,'yyyy-mm-dd')||' '||hr_overtime_line.start_time 开始日期时间, to_char(hr_overtime_line.overtime_date,'yyyy-mm-dd')||' '||hr_overtime_line.end_time 结束日期时间, hr_overtime_line.flexible_rest_duration 休息时长, coalesce(compensation_way_trans.value,compensation_way.name) 加班处理, hr_overtime.reason 加班事由, hr_overtime_line.employee_apply_duartion 申请加班小时数, hr_overtime_line.active_time 实际加班小时数, case when hr_attendance_details.clock_in_state <> 'normal' or hr_attendance_details.clock_out_state <> 'normal' or hr_attendance_details.clock_in_place_state is not null or hr_attendance_details.clock_out_place_state is not null then '异常' else '正常' end 说明, case when hr_overtime.x_bp_state='done' then '审批通过' when hr_overtime.x_bp_state='waiting' then '审批中' end 流程状态 from hr_overtime left join hr_employee emp on hr_overtime.employee_id=emp.id left join emp_job on emp.job_info_id=emp_job.id 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_business_unit on emp_job.business_unit_id=hr_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 dep_level_view on dep_level_view.cid=emp_job.department_id left join x_builder_zj on emp_job.x_zj=x_builder_zj.id left join hr_overtime_line on hr_overtime_line.overtime_id=hr_overtime_line.id left join hr_overtime_type on hr_overtime_type.id=hr_overtime_line.overtime_type left join ir_translation hr_overtime_type_trans on hr_overtime_type_trans.res_id=hr_overtime_type.id and hr_overtime_type_trans.name ='hr.overtime.type,name' and hr_overtime_type_trans.lang='zh_CN' left join compensation_way on hr_overtime_line.compensation_way=compensation_way.id left join ir_translation compensation_way_trans on compensation_way_trans.res_id=compensation_way.id and compensation_way_trans.name ='compensation.way,name' and compensation_way_trans.lang='zh_CN' left join hr_attendance_details on emp.id=hr_attendance_details.id and hr_attendance_details.attendance_date=hr_overtime_line.overtime_date where hr_overtime.x_bp_state in ('done','waiting') ================================================== WITH RECURSIVE dep_level_view AS ( select eroad_index_id as cid, dep_trans.value,code, ARRAY [hr_department.code] as code_list, ARRAY [case when dep_trans.value is not null then dep_trans.value else hr_department.name end] as name_list, ARRAY [eroad_index_id] as path_ids, FALSE as is_cycle from hr_department left join ir_translation dep_trans on dep_trans.res_id=hr_department.id and dep_trans.name ='hr.department,name' and dep_trans.lang='zh_CN' 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, dep_transt.value, dep_table.code, dep_level_view.code_list || dep_table.code as code_list, dep_level_view.name_list || case when dep_transt.value is not null then dep_transt.value else dep_table.name end 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 dep_transt on dep_transt.res_id=dep_table.id and dep_transt.name ='hr.department,name' and dep_transt.lang='zh_CN' 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 ) select coalesce(hr_department_trans.value,hr_department.name) 部门, emp.employee_number 员工工号, emp.name 姓名, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, array_to_string(dep_level_view.name_list,'/') 部门全路径, x_builder_zj.x_zj 职级, hr_attendance_adjustment.adjust_datetime 签卡时间, adjustment_reasons_classification.name 签卡原因, hr_attendance_adjustment.reason 签卡说明, emp.termination_date 离职日期 from hr_attendance_adjustment left join hr_employee emp on hr_attendance_adjustment.employee_id=emp.id left join emp_job on emp.job_info_id=emp_job.id 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_business_unit on emp_job.business_unit_id=hr_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 dep_level_view on dep_level_view.cid=emp_job.department_id left join x_builder_zj on emp_job.x_zj=x_builder_zj.id left join adjustment_reasons_classification on hr_attendance_adjustment.adjustment_reason_type=adjustment_reasons_classification.id where hr_attendance_adjustment.x_bp_state='done'