select a.role_name role_name,
regexp_replace(listagg(a.perm_name, ',') within group(order by a.perm_name),'([^,]*)(,\1)+($|,)','\1\3') perm_name,
regexp_replace(listagg(a.menu_name, ',') within group(order by a.menu_name),'([^,]*)(,\1)+($|,)','\1\3') menu_name
from (select tr.role_name, tbp.perm_name, tn.menu_name
from tsys_role tr
left join tsys_role_btn_perm trbp
on tr.role_id = trbp.role_id
left join tsys_btn_perm tbp
on trbp.perm_id = tbp.perm_id
left join tsys_menu tn
on tbp.menu_id = tn.menu_id) a
group by a.role_name;
orcale里的正则替换
regexp_replace(1,2,3,4,5,6)
语法说明:1:字段 2:替换的字段 3:替换成什么 4:起始位置(默认从1开始) 5:替换的次数(0是无限次) 6:不区分大小写