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 姓名, emp.name 姓名, coalesce(bu_trans.value,hr_business_unit.name) 所属业务单元, array_to_string(dep_level_view.name_list,'/') 部门全路径, x_builder_zj.x_zj 职级, to_char(hr_attendance.name,'yyyy-mm-dd') 考勤日期, hr_attendance.name 刷卡时间, case when hr_attendance.x_fromjs=True then '门禁' else '企业微信' end 刷卡方式, attendance_point.name 打卡地点, '' 是否为常驻办公点 from hr_attendance left join hr_employee emp on hr_attendance.employee_id=emp.id left join emp_job on emp.job_info_id=emp_job.id 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 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 x_builder_zj on emp_job.x_zj=x_builder_zj.id left join attendance_point on hr_attendance. address_id=attendance_point.id