代码之家 › 专栏 › 技术社区 › gregmac

使用SQL搜索一对多关系中的集合

search sql

gregmac · 技术社区 · 16 年前

根据表格:

权限:permissionid,name

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

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

如果没有,有没有比暴力方法更好的方法来简单地迭代每个角色,看看它是否具有确切的权限?

5 回复 | 直到 16 年前

Mehrdad Afshari 16 年前

您可以选择具有要查找的权限子集的所有角色。计算权限数,看看它是否与您需要的权限数完全相等:

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

James Piggot 16 年前

在对我对这个问题的第一个答案做了一个散列之后,这里有一个稍微偏左的字段替代方案,它可以工作,但确实涉及到向数据库添加数据。

诀窍是在权限表中添加一列,该列为每一行保存一个唯一的值。

这是一个相当常见的模式,将给出精确的结果。不利的一面是,您必须编写代码来隐藏数字等价物。

id int(10) 
name varchar(45) 
value int(10)

然后内容将变成:

Permission:           Role                   Role_Permission
id  name  value       id  name               roleid permissionid
--  ----  -----       --  ----               ------ ------------
1   Read     8         1   Admin                1          1
2   Write   16         2   DataAdmin            1          2
3   Update  32         3   User                 1          3
4   Delete  64                                  1          4
                                                2          1
                                                2          3
                                                2          4

然后,每个角色组合都会产生一个独特的价值:

SELECT x.roleid, sum(value) FROM role_permission x
inner join permission p
on x.permissionid = p.id
Group by x.roleid

给:

roleid   sum(value)
------   ----------
    1          120       (the sum of permissions 1+2+3+4 = 120)
    2          104       (the sum of permissions 1+3+4 = 104)

现在我把那个开瓶器放在哪里了。。。

JK. 16 年前

这是一个古老的sql技巧(至少在Oracle中有效):

SELECT roleid FROM role_permission t1
WHERE NOT EXISTS (
(SELECT permissionid FROM role_permission t2 WHERE t2.roleid = t1.roleid
 MINUS
 SELECT permissionid FROM role_permission WHERE roleid = 'Admin')
UNION
(SELECT permissionid FROM role_permission t2 WHERE roleid = 'Admin'
 MINUS
 SELECT permissionid FROM role_permsission t2 WHERE t2.roleid = t1.roleid)
)

Russ Cam 16 年前

基本上,您需要检查是否有一个角色具有与您正在检查的完全相同的不同权限数。

我在SQLServer2005上检查了这个存储过程,它只返回那些权限ID与传入的逗号分隔权限ID列表中的权限ID完全匹配的角色ID-

CREATE PROC get_roles_for_permissions (@list nvarchar(max)) -- @list is a comma separated list of your permission ids
AS
SET NOCOUNT ON

BEGIN

DECLARE     @index INT, @start_index INT, @id INT
DECLARE     @permission_ids TABLE (id INT)           

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN
                SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
                INSERT INTO @permission_ids ([id]) VALUES (@id)
                SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT)
    INSERT INTO @permission_ids ([id]) VALUES (@id)

SELECT 
r.roleid 
FROM
role r 
INNER JOIN 
role_permission rp 
ON r.roleid = rp.roleid
INNER JOIN
@permission_ids ids
ON
rp.permissionid = ids.id
GROUP BY r.roleid
HAVING(SELECT COUNT(*) 
       FROM role_permission 
       WHERE roleid = r.roleid) = (SELECT COUNT(*) FROM @permission_ids)

END

示例数据

CREATE TABLE [dbo].[role](
    [roleid] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50)
    )

CREATE TABLE [dbo].[permission](
    [permissionid] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50)
    )

CREATE TABLE [dbo].[role_permission](
    [roleid] [int],
    [permissionid] [int]
    )

INSERT INTO role(name) VALUES ('Role1')
INSERT INTO role(name) VALUES ('Role2')
INSERT INTO role(name) VALUES ('Role3')
INSERT INTO role(name) VALUES ('Role4')

INSERT INTO permission(name) VALUES ('Permission1')
INSERT INTO permission(name) VALUES ('Permission2')
INSERT INTO permission(name) VALUES ('Permission3')
INSERT INTO permission(name) VALUES ('Permission4')

INSERT INTO role_permission(roleid, permissionid) VALUES (1, 1)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 2)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (1, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 2)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (2, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (3, 3)
INSERT INTO role_permission(roleid, permissionid) VALUES (3, 4)
INSERT INTO role_permission(roleid, permissionid) VALUES (4, 4)

EXEC get_roles_for_permissions '3,4' -- RETURNS roleid 3

James Piggot 16 年前

也许可以使用一个子查询,沿着。。。

SELECT * FROM role r
WHERE r.rolid = (SELECT x.roledid 
                 FROM role_permission 
                 WHERE x.permissionid in (1,2,3,4);

很抱歉,我还没有验证这个问题,但是我刚刚花了一个小时调试了另一个问题的PHP代码,我觉得有必要喝一杯红酒。