sql - 使用SQL在一对多关系中搜索集合

Question

给定表格:

角色:roleid，名字
权限:permissionid，名称
role_permission: roleid, permissionid

我有一组权限，我想看看是否有一个现有角色拥有这些权限，或者我是否需要创建一个新角色。请注意，我已经知道 permissionid，所以实际上可以忽略权限表 - 为了清楚起见，我只是将它包含在这里。

这可以在 SQL 查询中完成吗？我想它必须是一个动态生成的查询。

如果没有，是否有比简单地遍历每个角色并查看它是否具有确切权限的蛮力方法更好的方法？

请注意，我正在寻找具有确切权限集的角色 - 不多也不少。

Answer 1

您可以选择所有具有您正在寻找的权限子集的角色。统计权限数，看是否正好等于你需要的权限数:

select r.roleid 
from role r
where not exists (select * from role_permissions rp where rp.roleid = r.roleid and rp.permissionid not in (1,2,3,4)) -- id of permissions
   and (select count(*) from role_permissions rp where rp.roleid = r.roleid) = 4 -- number of permissions