mysql - 通过比较列值的 id 聚合行

Question

我有下表按用户的权限对用户进行分组

userIds     permissions
4,5,7,8     100,1600,500,501,502,400,401,1500,1501

权限列中的数字是部分 ID。

其中一些部分可能与我检索并存储在另一个表中的其他数据相关。

sectionId   userId  resourceId
100     4   NULL
1600    4   NULL
500     4   NULL
501     4   NULL
502     4   NULL
400     4   NULL
401     4   1
1500    4   NULL
1501    4   NULL
100     5   NULL
1600    5   NULL
500     5   NULL
501     5   NULL
502     5   NULL
400     5   NULL
401     5   1,2
1500    5   NULL
1501    5   NULL
100     7   NULL
1600    7   NULL
500     7   NULL
501     7   NULL
502     7   NULL
400     7   NULL
401     7   2
1500    7   NULL
1501    7   NULL
100     8   NULL
1600    8   NULL
500     8   NULL
501     8   NULL
502     8   NULL
400     8   NULL
401     8   1
1500    8   NULL
1501    8   NULL

我的目标是对第一个表的 userIds 列中的每个用户(以逗号分隔)与第二个表的每一行进行比较，以检查每个用户对于该特定的sectionId是否具有相同的resourceId值。

如果一个或多个用户的每个部分具有相同的resourceId 值，我希望将他们分组在一起，否则他们需要位于不同的行上。

这是我期望从提供的示例数据中得到的输出:

userIds     permissions
4,8     100,1600,500,501,502,400,401,1500,1501
5   100,1600,500,501,502,400,401,1500,1501
7   100,1600,500,501,502,400,401,1500,1501

更新我设法通过以下方式获得所需的输出:

-- Numbers table creation
DROP temporary TABLE IF EXISTS tally;

CREATE temporary TABLE tally
  (
     n INT NOT NULL auto_increment PRIMARY KEY
  );

INSERT INTO tally
            (n)
SELECT NULL
FROM   (SELECT 0 AS N
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
        UNION ALL
        SELECT 8
        UNION ALL
        SELECT 9) a,
       (SELECT 0 AS N
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
        UNION ALL
        SELECT 8
        UNION ALL
        SELECT 9) b;

-- Split users by comma from first table
DROP temporary TABLE IF EXISTS tmppermissions2;

CREATE temporary TABLE tmppermissions2
  (
     userid      VARCHAR(255) NOT NULL,
     permissions TEXT NOT NULL
  );

INSERT INTO tmppermissions2
SELECT userid,
       permissions
FROM   (SELECT Substring_index(Substring_index(t.userids, ',', tally.n), ',', -1
               )
                      userId,
               t.permissions
                      permissions
        FROM   tally
               INNER JOIN tmppermissions t
                       ON Char_length(t.userids) - Char_length(
                                                   REPLACE(t.userids, ',',
                                                   '')) >=
                          tally.n - 1
        ORDER  BY n) AS split;

-- Gets the users with the same permissions
DROP temporary TABLE IF EXISTS sharedprofiles;

CREATE temporary TABLE sharedprofiles
  (
     userids     VARCHAR(255) NOT NULL,
     permissions TEXT NOT NULL,
     profileid   INT(11)
  );

INSERT INTO sharedprofiles
SELECT Group_concat(userid),
       permissions,
       NULL
FROM   tmppermissions2
WHERE  userid NOT IN (SELECT split.userid
                      FROM   (SELECT Substring_index(Substring_index(r.userids,
                                                     ',',
                                                     t.n), ',', -1)
                                     userId
                              FROM   tally t
                                     INNER JOIN tmppermissions r
                                             ON Char_length(r.userids)
                                                - Char_length(
                                                REPLACE(r.userids, ',',
                                                '')) >=
                                                t.n - 1
                              WHERE  Position(',' IN r.userids) > 0
                              ORDER  BY n) AS split
                      WHERE  split.userid IN (SELECT *
                                              FROM   (SELECT Group_concat(userid
                                                             ORDER
                                                             BY userid ASC)
                                                             AS
                                                             users
                                                      FROM
                                             tmpcurrentresources2
                                                      GROUP  BY resourceid,
                                                                sectionid
                                                      ORDER  BY users) b
                                              WHERE  Position(',' IN b.users) =
                                                     0))
GROUP  BY permissions
ORDER  BY Group_concat(userid);

-- Gets the users with specific permissions
DROP temporary TABLE IF EXISTS singleprofiles;

CREATE temporary TABLE singleprofiles
  (
     userid      VARCHAR(255) NOT NULL,
     permissions TEXT NOT NULL,
     profileid   INT(11)
  );

INSERT INTO singleprofiles
SELECT userid,
       permissions,
       NULL
FROM   tmppermissions2
WHERE  userid IN (SELECT split.userid
                  FROM   (SELECT Substring_index(Substring_index(r.userids, ',',
                                                 t.n),
                                 ',', -1)
                                 userId
                          FROM   tally t
                                 INNER JOIN tmppermissions r
                                         ON Char_length(r.userids) -
                                            Char_length(
                                            REPLACE(r.userids, ',',
                                            '')) >=
                                            t.n - 1
                          WHERE  Position(',' IN r.userids) > 0
                          ORDER  BY n) AS split
                  WHERE  split.userid IN (SELECT *
                                          FROM   (SELECT Group_concat(userid
                                                         ORDER BY
                                                         userid ASC)
                                                         AS
                                                         users
                                                  FROM   tmpcurrentresources2
                                                  GROUP  BY resourceid,
                                                            sectionid
                                                  ORDER  BY users) b
                                          WHERE  Position(',' IN b.users) = 0))
ORDER  BY userid;

-- Merge the results
SELECT *
FROM  sharedprofiles
UNION
SELECT *
FROM  singleprofiles; 

我想知道是否有更简洁的方法来实现相同的结果。

Answer 1

解决方案(我怀疑您已经知道)是标准化您的架构。

所以而不是...

userIds     permissions
4,5         100,1600,500

...你可能有

userIds     permissions
      4             100
      4            1600
      4             500
      5             100
      5            1600
      5             500