SQL如何不使用STRING_AGG按用户所属群组组合分组并选取任一用户

优化实现方案（无需STRING_AGG） #

你可以通过集合匹配的方式实现需求，相比字符串拼接的方案性能更优，同时不存在分隔符冲突的潜在问题，还能兼容SQL Server 2017之前的版本：

;WITH UserGroupCount AS (
    -- 先统计每个用户所属的群组数量，用于快速过滤集合大小不一致的用户
    SELECT UserId, COUNT(GroupId) AS GroupCnt
    FROM @UserGroup
    GROUP BY UserId
)
SELECT 
    -- 按需生成群组组合的展示Key，不需要的话可以删除这部分逻辑
    STUFF(
        (SELECT ',Group' + CAST(GroupId AS VARCHAR(10)) 
         FROM @UserGroup ug WHERE ug.UserId = u.UserId 
         ORDER BY GroupId FOR XML PATH('')),
    1,1,'(') + ')' AS [Key(group combination)],
    u.UserId AS [User Id]
FROM UserGroupCount u
WHERE NOT EXISTS (
    -- 筛选规则：不存在用户ID更小、且群组集合和当前用户完全一致的用户
    SELECT 1
    FROM UserGroupCount u2
    WHERE u2.UserId < u.UserId
    AND u2.GroupCnt = u.GroupCnt
    AND NOT EXISTS (
        -- 校验u2的所有群组都在u的群组列表中
        SELECT 1 FROM @UserGroup ug2 
        WHERE ug2.UserId = u2.UserId
        AND NOT EXISTS (
            SELECT 1 FROM @UserGroup ug 
            WHERE ug.UserId = u.UserId AND ug.GroupId = ug2.GroupId
        )
    )
)

方案优势 #

• 避免了STRING_AGG的字符串拼接、排序开销，数据量越大性能优势越明显，配合UserId+GroupId的联合索引可以做到秒级查询十万级以上的关联数据
• 没有分隔符冲突风险：如果GroupId取值包含你用来拼接的分隔符，STRING_AGG方案会出现不同群组组合被识别为同一类的错误，集合匹配的逻辑完全基于数值对比，不存在这个问题
• 兼容低版本SQL Server：STRING_AGG是SQL Server 2017才引入的函数，这个方案可以在2016及更早的版本上运行
• 灵活性更高：如果不需要输出群组组合的展示字符串，直接删掉Key(group combination)对应的生成逻辑即可，性能还会进一步提升

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