SQL Server 逗号分隔列使用IN/LIKE实现值匹配的查询方法

核心思路 #

表2的[Group]字段存储逗号分隔的多值，我们可以通过拆分多值为单行单值关联或者字符串模糊匹配两种方式实现需求，两种方式均兼容示例数据场景，输出结果和预期一致。

注意：GROUP是SQL Server保留关键字，所有字段引用时必须用方括号[]包裹避免语法报错。

方案1：拆分多值关联（推荐，SQL Server 2016及以上） #

SQL Server 2016开始内置STRING_SPLIT函数，可直接拆分逗号分隔字符串，查询性能和准确性更高：

SELECT DISTINCT t1.Id, t1.[Group]
FROM Table1 t1
INNER JOIN (
    -- 拆分表2的多值Group字段为单行单值，同时修剪值前的空格
    SELECT LTRIM(value) AS GroupVal
    FROM Table2 t2
    CROSS APPLY STRING_SPLIT(t2.[Group], ',')
) t2_split ON t1.[Group] = t2_split.GroupVal
ORDER BY t1.Id

低版本SQL Server（2016以下）适配 #

如果使用不支持STRING_SPLIT的低版本，先自定义拆分函数即可：

-- 自定义字符串拆分函数
CREATE FUNCTION dbo.SplitString
(
    @InputString NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value NVARCHAR(1000))
AS
BEGIN
    DECLARE @Start INT, @End INT
    SET @Start = 1
    IF SUBSTRING(@InputString, LEN(@InputString), 1) <> @Delimiter
        SET @InputString = @InputString + @Delimiter
    WHILE CHARINDEX(@Delimiter, @InputString, @Start) > 0
    BEGIN
        SET @End = CHARINDEX(@Delimiter, @InputString, @Start)
        INSERT INTO @Result (Value)
        VALUES (LTRIM(RTRIM(SUBSTRING(@InputString, @Start, @End - @Start))))
        SET @Start = @End + 1
    END
    RETURN
END

调用自定义函数的查询语句：

SELECT DISTINCT t1.Id, t1.[Group]
FROM Table1 t1
INNER JOIN (
    SELECT Value AS GroupVal
    FROM Table2 t2
    CROSS APPLY dbo.SplitString(t2.[Group], ',')
) t2_split ON t1.[Group] = t2_split.GroupVal
ORDER BY t1.Id

方案2：字符串匹配（无需拆分，写法更简洁） #

如果不想使用拆分函数，也可以通过拼接逗号避免部分匹配，实现需求：

SELECT t1.Id, t1.[Group]
FROM Table1 t1
WHERE EXISTS (
    SELECT 1 
    FROM Table2 t2
    -- 前后拼接逗号避免 Group1 匹配 Group11 的错误场景，同时移除空格适配空白
    WHERE ',' + REPLACE(t2.[Group], ' ', '') + ',' LIKE '%,' + t1.[Group] + ',%'
)
ORDER BY t1.Id

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