我有个用户角色表，一个用户可以分配多个角色。我想要一个查询，它将检查一个特定的用户是否分配了'管理员'或'超级管理员'角色（检查更多的角色可能需要在未来）。我尝试使用plsql来完成这项工作，为用户选择每个角色并将其传递给一个变量，然后检查这些选择的角色中是否有“admin”或“superadmin”。事情变得有点复杂了。我如何解决这个问题？
xzlaal3s1#
像这样的？
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 noSQL>
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>
vu8f3i0k2#
如果需要特定用户，可以执行以下操作：
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_rolefrom dual
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
2条答案
按热度按时间xzlaal3s1#
像这样的？
vu8f3i0k2#
如果需要特定用户，可以执行以下操作：