WITH PERFORM_DEPART AS ( SELECT ID,DEPARTS,DEPARTIDS FROM ( SELECT ID, DEPARTS,DEPARTIDS,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY LENGTH(DEPARTS) DESC)RN FROM ( SELECT T.ID, T.DEPART_TYPE, SUBSTR( SYS_CONNECT_BY_PATH ( T.DEPART_DESC, ',' ), 2 ) DEPARTS, SUBSTR( SYS_CONNECT_BY_PATH ( T.ID, ',' ), 2 ) DEPARTIDS FROM U_SAFETY_PERFORM_DEPART T START WITH T.DEPART_DESC IS NOT NULL CONNECT BY PRIOR T.DEPART_DESC = T.UP_DEPART ) )WHERE RN=1 ), PERMISSION_BASE AS ( SELECT A.ID, A.EMPLID, A.NAME, B.DEPARTS, B.DEPARTIDS, A.ROLE_TYPE, A.CREATE_BY, A.CREATE_TIME, A.UPDATE_BY, A.UPDATE_TIME FROM "U_SAFETY_PERFORM_PERMISSION" A LEFT JOIN PERFORM_DEPART B ON A.DEPART_ID = B.ID WHERE A.ID='1876827123776999426' ), DEPART_DATA AS ( SELECT ID, max(DECODE(lvl,'1',DEPARTIDS,''))BG, max(DECODE(lvl,'2',DEPARTIDS,''))BU, max(DECODE(lvl,'3',DEPARTIDS,''))CENTER FROM( SELECT ID, REGEXP_SUBSTR(DEPARTIDS, '[^,]+', 1, LEVEL) AS DEPARTIDS, level lvl FROM PERMISSION_BASE CONNECT BY REGEXP_SUBSTR(DEPARTIDS, '[^,]+', 1, LEVEL) IS NOT NULL AND PRIOR dbms_random.value IS NOT NULL AND PRIOR DEPARTIDS = DEPARTIDS ORDER BY LEVEL ) GROUP BY id ) SELECT A.*,B.BG,B.BU,B.CENTER FROM PERMISSION_BASE A INNER JOIN DEPART_DATA B ON A.ID=B.ID