SQL Server中用STUFF实现同表两列逗号分隔值的交集查询

实现逻辑 #

核心流程分三步：

• 拆分每行pertinent、procedure字段的逗号分隔字符串为独立编码行
• 内关联合并两个拆分后的编码集，筛选出相等的公共编码，即为两列的交集
• 借助STUFF函数配合FOR XML PATH将公共编码重新拼接为逗号分隔字符串，无匹配值时自动返回NULL

注意：procedure是SQL Server保留关键字，写查询时需要用方括号[]包裹避免语法错误

可直接运行的完整代码 #

-- 1. 构建测试表插入示例数据
CREATE TABLE #TmpData (
    id INT,
    pertinent VARCHAR(MAX),
    [procedure] VARCHAR(MAX)
);
INSERT INTO #TmpData
VALUES
(1, '13271,13272,513008,513009', '13200,13271,19353,21101,21105,21140'),
(2, '18236', '18235,19290,19749,21102,21105,21140');

-- 2. 核心查询计算交集
SELECT
    id,
    pertinent,
    [procedure],
    STUFF(
        (
            SELECT ',' + p.value
            FROM STRING_SPLIT(pertinent, ',') p
                INNER JOIN STRING_SPLIT([procedure], ',') pr
                    ON p.value = pr.value
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'),
        1,
        1,
        ''
    ) AS [procedures pertinents]
FROM #TmpData;

运行结果 #

兼容性说明 #

如果使用SQL Server 2016以下版本，内置STRING_SPLIT函数不可用，可自定义字符串拆分表值函数替换即可，STUFF拼接交集的核心逻辑无需调整。

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