oracleÐÐÁÐ×Ö·û´®

SELECT T.USER_NO,
       T.USER_NAME,
       MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.ROLE_NAME, '/'), 2))
  from (SELECT T.USER_NO,
               T.USER_NAME,
               T.ROLE_NAME,
               T.USER_NO || ROW_NUMBER() OVER(PARTITION BY T.USER_NO ORDER BY T.USER_NO, LENGTH(T.ROLE_NAME)) RN,
               T.USER_NO ||
               (ROW_NUMBER() OVER(PARTITION BY T.USER_NO ORDER BY T.USER_NO,
                                  LENGTH(T.ROLE_NAME)) + 1) RN_DOWN
          from AAAAA T
        ) T
 START WITH RN LIKE '%1'
CONNECT BY RN = PRIOR RN_DOWN
 GROUP BY T.USER_NO, T.USER_NAME
 ORDER BY T.USER_NO
---Ô­±¾Êý¾Ý
---ת»»ºóµÄÊý¾Ý