select a.MENU_NAME,a.MENU_PROMISSIONID, wm_concat(b.PROMISSION_NAME) from Backstage_menu a,menu_promission b where ','||a.MENU_PROMISSIONID||',' like '%,'||b.PROMISSION_ID||',%' group by a.MENU_NAME,a.MENU_PROMISSIONID
...看错了select a.MENU_NAME,b.MENU_PROMISSIONID, wm_concat(c.PROMISSION_NAME) from Backstage_menu a,menu_promission c,operator_Competence b where ','||b.MENU_PROMISSIONID||',' like '%,'||c.PROMISSION_ID||',%' and a.menu_id =b.menu_id and a.menu_parentid='1' group by a.MENU_NAME,b.MENU_PROMISSIONID
谢谢解答 但是 提示 wm_concat 无效标识符
10g以上的版本才支持wm_concat 试试select menu_name,menu_promissionid, substr(max(sys_connect_by_path(promission_name,',')),2) promission_name from( select a.MENU_NAME,b.MENU_PROMISSIONID,c.promission_name,a.menu_id, row_number()over(partition by a.menu_id order by c.PROMISSION_ID)rn from Backstage_menu a,menu_promission c,operator_Competence b where ','||b.MENU_PROMISSIONID||',' like '%,'||c.PROMISSION_ID||',%' and a.menu_id =b.menu_id and a.menu_parentid='1') start with rn=1 connect by prior menu_id=menu_id and prior rn=rn-1 group by MENU_NAME,MENU_PROMISSIONID
wm_concat(b.PROMISSION_NAME)
from Backstage_menu a,menu_promission b
where ','||a.MENU_PROMISSIONID||',' like '%,'||b.PROMISSION_ID||',%'
group by a.MENU_NAME,a.MENU_PROMISSIONID
wm_concat(c.PROMISSION_NAME)
from Backstage_menu a,menu_promission c,operator_Competence b
where ','||b.MENU_PROMISSIONID||',' like '%,'||c.PROMISSION_ID||',%'
and a.menu_id =b.menu_id
and a.menu_parentid='1'
group by a.MENU_NAME,b.MENU_PROMISSIONID
试试select menu_name,menu_promissionid,
substr(max(sys_connect_by_path(promission_name,',')),2) promission_name
from(
select a.MENU_NAME,b.MENU_PROMISSIONID,c.promission_name,a.menu_id,
row_number()over(partition by a.menu_id order by c.PROMISSION_ID)rn
from Backstage_menu a,menu_promission c,operator_Competence b
where ','||b.MENU_PROMISSIONID||',' like '%,'||c.PROMISSION_ID||',%'
and a.menu_id =b.menu_id
and a.menu_parentid='1')
start with rn=1
connect by prior menu_id=menu_id and prior rn=rn-1
group by MENU_NAME,MENU_PROMISSIONID