http://seandeng888.iteye.com/blog/2110590 学习了
需求 查询用户对应的所有角色
用户A 角色1,角色2....
需求 查询用户对应的所有角色
用户A 角色1,角色2....
SELECT USER_CODE, LTRIM(MAX(sys_connect_by_path(ROLE_NAME, ',')), ',') VALS
from (select U.USER_CODE USER_CODE,
R.ROLE_NAME ROLE_NAME,
row_number() over(partition by USER_CODE order by USER_CODE) rn
FROM TB_EMM_USER U, TB_EMM_USER_ROLE UR, TB_EMM_ROLE R
WHERE U.USER_ID = UR.USER_ID
AND UR.ROLE_ID = R.ID)
start with rn = 1
connect by rn - 1 = prior rn
AND USER_CODE = PRIOR USER_CODE
group by USER_CODE
order by USER_CODE