像这样的？

SQL> with user_role (user_id, role) as
  2    (select 1, 'admin'      from dual union all
  3     select 1, 'superadmin' from dual union all
  4     select 2, 'clerk'      from dual union all
  5     select 2, 'superadmin' from dual union all
  6     select 3, 'manager'    from dual
  7    )
  8  select user_id,
  9    max (case when role in ('admin', 'superadmin') then 'yes' else 'no' end ) is_admin
 10  from user_role
 11  group by user_id
 12  order by user_id;

   USER_ID IS_ADMIN
---------- ----------
         1 yes
         2 yes
         3 no

SQL>

如果需要特定用户，可以执行以下操作：

select (case when exists (select 1
                          from user_role ur
                          where ur.role in ('admin', 'superadmin') and user_id = :user_id
                         )
             then 'yes' else 'no'
        end) as has_role
from dual