mysql：从组描述中重新查找用户组

46qrfjad 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(231)

我在查找用户的组成员身份时遇到问题(是的，不是很清楚）
例如：
我有两张table：
-其中一个包含具有其权限的用户列表：

userId | permission
-------|-----------
1      | build
1      | play
1      | jump
2      | build
2      | jump
2      | run
3      | drink
3      | build
4      | run

-第二个表包含组和him权限：

groupId | permission
--------|-----------
G1      | build
G1      | jump
G2      | play
G2      | jump
G3      | drink
G3      | run
G4      | drink
G5      | build

我的目标是找到用户可以拥有的所有组：

userId | groupId
-------|-----------
1      | G1
1      | G2
1      | G5
2      | G1
2      | G5
3      | G4
3      | G5

我创建了一个请求来查找属于该组的用户，但我不能为我的所有组执行此操作（我的数据集中有1000多个组）：

SELECT DISTINCT userId
FROM (
       SELECT userId, count(*) AS nbData
       from table_a
       WHERE permission in (
         SELECT permission
         from table_b
         where groupId = 'g1'
       )
       group by userId
     ) as t
where nbData = (SELECT count(*) from table_b where groupId = 'g1');

如果用户拥有某个组的所有权限，则该用户属于该组。目标是找到每个用户的每个组

sql mysql Join group-by

来源：https://stackoverflow.com/questions/53752059/mysql-refind-groups-of-user-from-group-description

3条答案

按热度按时间

e1xvtsh31#

像这样的方法可能有用：

SELECT DISTINCT
    a.userId,
    b.groupId
FROM
    table_a a
    JOIN table_b b 
        ON a.permission = b.permission

赞(0）回复(0）举报 2021-06-18

pkwftd7m2#

如果两个表中都没有重复项，则可以在“权限”和“聚合”中将这些表连接在一起：

select up.userId, gp.groupId
from user_permissions up join
     group_permissions gp
     on up.permission = gp.permission
group by up.userId, gp.groupId
having count(*) = (select count(*)
                   from group_permissions gp2
                   where gp2.groupId = gp.groupId
                  );

如果该组的所有权限都匹配，则该用户在该组中。

赞(0）回复(0）举报 2021-06-18

slwdgvem3#

例如。：

DROP TABLE IF EXISTS user_permissions;

CREATE TABLE user_permissions
(user_id INT NOT NULL
,permission VARCHAR(12) NOT NULL
,PRIMARY KEY(user_id,permission)
);

INSERT INTO user_permissions VALUES
(1,'build'),
(1,'play'),
(1,'jump'),
(2,'build'),
(2,'jump'),
(2,'run'),
(3,'drink'),
(3,'build'),
(4,'run');

DROP TABLE IF EXISTS group_permissions;

CREATE TABLE group_permissions
(group_id INT NOT NULL
,permission VARCHAR(12) NOT NULL
,PRIMARY KEY(group_id,permission)
);

INSERT INTO group_permissions VALUES
(101,'build'),
(101,'jump'),
(102,'play'),
(102,'jump'),
(103,'drink'),
(103,'run'),
(104,'drink'),
(105,'build');

SELECT DISTINCT u.user_id
              , g.group_id 
           FROM user_permissions u 
           JOIN group_permissions g 
             ON g.permission = u.permission -- groups that users potentially belong to
           LEFT 
           JOIN 
              ( SELECT DISTINCT x.user_id
                              , y.group_id 
                           FROM user_permissions x 
                           LEFT 
                           JOIN group_permissions y 
                             ON y.permission = x.permission 
                           LEFT 
                           JOIN group_permissions z 
                             ON z.group_id = y.group_id 
                           LEFT 
                           JOIN user_permissions a 
                             ON a.user_id = x.user_id 
                            AND a.permission = z.permission 
                          WHERE a.user_id IS NULL
              ) n -- groups that users don't belong to (this could be much simpler ;-) )
             ON n.user_id = u.user_id 
            AND n.group_id = g.group_id 
          WHERE n.user_id IS NULL;
+---------+----------+
| user_id | group_id |
+---------+----------+
|       1 |      101 |
|       1 |      105 |
|       1 |      102 |
|       2 |      101 |
|       2 |      105 |
|       3 |      105 |
|       3 |      104 |
+---------+----------+

赞(0）回复(0）举报 2021-06-17

我来回答

mysql：从组描述中重新查找用户组

3条答案

相关问题

热门标签

最新问答