如何从UserItems表筛选权限组相同的用户以配置角色？

要找出拥有完全相同权限组集合的用户，核心是先为每个用户生成其权限组的唯一特征值，再筛选出特征值重复的用户组。以下是针对SQL Server的实现方案：

方法1：适用于SQL Server 2017及以上版本（使用STRING_AGG） #

-- 第一步：生成每个用户的权限组特征串，按权限组ID排序保证一致性
WITH UserPermissionGroups AS (
    SELECT 
        UserId,
        -- 按PermissionGroupId排序后拼接，确保相同集合的特征串一致
        STRING_AGG(PermissionGroupId, ',') WITHIN GROUP (ORDER BY PermissionGroupId) AS PermissionSet
    FROM dbo.UserItems
    WHERE PermissionGroupId IS NOT NULL -- 排除无权限组的记录
    GROUP BY UserId
),
-- 第二步：找出存在重复的权限组特征串
DuplicatePermissionSets AS (
    SELECT PermissionSet
    FROM UserPermissionGroups
    GROUP BY PermissionSet
    HAVING COUNT(*) > 1
)
-- 第三步：关联得到所有拥有重复权限组集合的用户及对应的权限组信息
SELECT 
    upg.UserId,
    upg.PermissionSet,
    ui.PermissionGroupId,
    pg.PermissionGroupName, -- 如果需要权限组名称，需关联PermissionGroups表
    ui.CanModify,
    ui.ItemOrganizationId
FROM UserPermissionGroups upg
JOIN DuplicatePermissionSets dps ON upg.PermissionSet = dps.PermissionSet
JOIN dbo.UserItems ui ON upg.UserId = ui.UserId
LEFT JOIN dbo.PermissionGroups pg ON ui.PermissionGroupId = pg.PermissionGroupId
ORDER BY upg.PermissionSet, upg.UserId, ui.PermissionGroupId;

方法2：适用于SQL Server 2016及以下版本（使用STUFF+FOR XML PATH） #

如果你的SQL Server版本不支持STRING_AGG，可以用传统的字符串拼接方式：

WITH UserPermissionGroups AS (
    SELECT 
        UserId,
        STUFF(
            (SELECT ',' + CAST(PermissionGroupId AS VARCHAR(10))
             FROM dbo.UserItems ui2
             WHERE ui2.UserId = ui1.UserId AND ui2.PermissionGroupId IS NOT NULL
             ORDER BY ui2.PermissionGroupId
             FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
            1, 1, ''
        ) AS PermissionSet
    FROM dbo.UserItems ui1
    WHERE ui1.PermissionGroupId IS NOT NULL
    GROUP BY ui1.UserId
),
DuplicatePermissionSets AS (
    SELECT PermissionSet
    FROM UserPermissionGroups
    GROUP BY PermissionSet
    HAVING COUNT(*) > 1
)
SELECT 
    upg.UserId,
    upg.PermissionSet,
    ui.PermissionGroupId,
    pg.PermissionGroupName,
    ui.CanModify,
    ui.ItemOrganizationId
FROM UserPermissionGroups upg
JOIN DuplicatePermissionSets dps ON upg.PermissionSet = dps.PermissionSet
JOIN dbo.UserItems ui ON upg.UserId = ui.UserId
LEFT JOIN dbo.PermissionGroups pg ON ui.PermissionGroupId = pg.PermissionGroupId
ORDER BY upg.PermissionSet, upg.UserId, ui.PermissionGroupId;

关键说明 #

• 原有SQL的问题：你之前的查询是按单个PermissionGroupId分组统计用户数，只能找出共享同一单个权限组的用户，无法识别那些拥有完全相同的权限组集合的用户（比如用户A有420+426，用户B也有420+426，这种情况原有SQL无法捕捉）。
• 排序的必要性：拼接权限组ID时必须排序，否则同一集合的不同顺序会生成不同特征串（比如420,426和426,420会被误判为不同集合）。
• 空值处理：如果允许用户无权限组，可根据实际需求调整WHERE PermissionGroupId IS NOT NULL条件。

内容的提问来源于stack exchange，提问作者tvb27275454