SQL查询：匹配临时表JSON字段值并将多行role_id合并为单行

你的原查询出错的核心原因有两个：

• #permission表的permission_id是char(6)定长类型，'-336'这类短值存储时尾部会自动补空格，你拼接LIKE匹配字符串时没有去掉尾部空格，直接导致匹配失败；
• 用LIKE模糊匹配JSON字段内容存在误判风险，比如-336可能会误匹配到-3361这类相似值，精准匹配更推荐用SQL Server原生的JSON解析函数。

适配SQL Server 2016及以上版本（推荐方案） #

SQL Server 2016开始原生支持JSON解析和STRING_AGG字符串拼接函数，实现逻辑如下：

SELECT
    p.permission_id,
    STRING_AGG(CAST(s.role_id AS VARCHAR(10)), ', ') AS role_id
FROM #permission p
INNER JOIN #source_audit s
    -- 如果实际存储的value本身就是正常双引号，可以去掉REPLACE函数
    ON JSON_VALUE(REPLACE(s.value, '"', '"'), '$.PermissionId') = TRIM(p.permission_id)
    AND JSON_VALUE(REPLACE(s.value, '"', '"'), '$.ConstraintTypeId') = 7
GROUP BY p.permission_id

执行后输出结果和你预期的完全一致。

适配SQL Server 2016以下低版本方案 #

如果你的数据库版本不支持STRING_AGG和JSON函数，可以用以下方案兼容：

SELECT
    p.permission_id,
    STUFF((
        SELECT ', ' + CAST(s2.role_id AS VARCHAR(10))
        FROM #source_audit s2
        WHERE s2.value LIKE '%"PermissionId":' + TRIM(p.permission_id) + ',%'
        AND s2.value LIKE '%"ConstraintTypeId":7%'
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS role_id
FROM #permission p
WHERE EXISTS (
    SELECT 1 FROM #source_audit s
    WHERE s.value LIKE '%"PermissionId":' + TRIM(p.permission_id) + ',%'
    AND s.value LIKE '%"ConstraintTypeId":7%'
)
GROUP BY p.permission_id

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