SQL Server 2017中拆分字符串并关联查询用户的方案咨询

针对你的需求，优先使用CTE（公共表表达式）结合SQL Server 2017的STRING_SPLIT和STRING_AGG函数实现，这种方案代码简洁、可读性高，无需额外维护存储过程，完全满足你的需求。

实现步骤与代码示例 #

1. 拆分Allowed字段的逗号分隔分组：用STRING_SPLIT将每个SecCode对应的分组拆分为行数据
2. 关联Users表获取分组对应的用户：通过字符串匹配关联用户表
3. 合并用户为逗号分隔字符串：用STRING_AGG将每个SecCode对应的所有用户合并为单个字段

完整SQL代码如下：

WITH SplitSecurityGroups AS (
    -- 拆分Security表的Allowed字段为单个分组
    SELECT 
        s.SecCode,
        TRIM(value) AS GroupName  -- 去除分组名前后可能的空格
    FROM Security s
    CROSS APPLY STRING_SPLIT(s.Allowed, ',')
),
GroupUsers AS (
    -- 关联Users表，获取每个分组对应的用户
    SELECT 
        s.SecCode,
        u.Users AS UserName
    FROM SplitSecurityGroups s
    JOIN Users u 
        ON u.GroupList LIKE '%' + s.GroupName + '%'  -- 匹配用户所属分组
)
-- 合并每个SecCode的用户为逗号分隔字符串
SELECT 
    SecCode,
    STRING_AGG(UserName, ',') AS Allowed  -- 可选：按用户名字母排序，添加 WITHIN GROUP (ORDER BY UserName)
FROM GroupUsers
GROUP BY SecCode
ORDER BY SecCode;

方案对比 #

• CTE方案：

  • 优势：逻辑清晰、代码可复用性强，可直接嵌入其他查询或报表中，无需额外对象维护
  • 适用场景：需求逻辑固定、无需参数化调用的场景

• 存储过程方案：

  • 优势：适合需要多次调用、需要参数化（比如指定特定SecCode）的场景，可封装复杂逻辑
  • 劣势：增加数据库对象维护成本，灵活性不如CTE

• 优化版关联方案：
  若Users表的GroupList也是逗号分隔格式，可改用精准匹配替代LIKE，提升性能：

  -- 更精准的关联逻辑
  JOIN Users u 
  CROSS APPLY STRING_SPLIT(u.GroupList, ',') ug
  ON TRIM(ug.value) = s.GroupName
  

注意事项 #

• 确认函数兼容性：STRING_SPLIT和STRING_AGG仅支持SQL Server 2017及以上版本
• 空值处理：若分组无对应用户，STRING_AGG会返回NULL，可改用ISNULL(STRING_AGG(UserName, ','), '')返回空字符串
• 性能优化：数据量较大时，建议给Security.SecCode、Users.GroupList创建合适的索引

内容的提问来源于stack exchange，提问作者Jason H.