我知道它可以优化，至少in关键字就可以。但是我觉得还应该有更好的。。
谁来试试看？
SELECT MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,max(CANEDIT) as CANEDIT,MODSORT
FROM (
SELECT AA.*,BB.CANEDIT
FROM(
SELECT a.MODID,a.MODNAME,a.PARENTMODID,a.RELATIVEURL,a.DISPLAYORDER,b.MODLEVEL,b.modsort
FROM AP_MODS a, TEMP_MOD b
WHERE a.MODID = b.MODID(+)
) AA,AP_MODS_ROLES BB
WHERE AA.MODID = BB.MODID(+)
AND BB.ROLEID in (select ROLEID from AP_USERS_APPS where USERID='1111') -- role

)
GROUP BY MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,MODSORT
ORDER BY MODLEVEL,MODSORT;