SQL使用CTE结果匹配4个字段生成新列报错如何解决？

问题根源 #

• 变量逻辑错误：@N 是单个字符串类型变量，即使你拼接出了'A','B','C','D','F'的格式，SQL 中的 IN 运算符也会将其视为一个完整的字符串值，不会自动解析为多个独立的枚举值，因此CAT_NAM_1 IN (@N)本质是判断字段是否等于整个@N字符串，完全不符合需求，还会触发语法或类型匹配报错。
• CTE 作用域问题：CTE（公用表表达式）的作用域仅限紧随其后的第一条SQL语句，你用CTE完成@N赋值后，该CTE就已经销毁，后续查询无法再直接调用。

最优实现方案 #

建议将合法名称存入临时表复用，性能和可读性都更高：

-- 提取当前年符合条件的分类名称存入临时表
SELECT NAME INTO #VALID_CAT_NAMES
FROM TABLE1
WHERE YEAR(DATE) = YEAR(GETDATE())
AND ID IN (20,23,25,30,37)

-- 按优先级匹配table2的分类字段
SELECT 
    CASE 
        WHEN CAT_NAM_1 IN (SELECT NAME FROM #VALID_CAT_NAMES) THEN CAT_NAM_1
        WHEN CAT_NAM_2 IN (SELECT NAME FROM #VALID_CAT_NAMES) THEN CAT_NAM_2
        WHEN CAT_NAM_3 IN (SELECT NAME FROM #VALID_CAT_NAMES) THEN CAT_NAM_3
        WHEN CAT_NAM_4 IN (SELECT NAME FROM #VALID_CAT_NAMES) THEN CAT_NAM_4
    END AS CAT
FROM table2

-- 用完记得删除临时表
DROP TABLE IF EXISTS #VALID_CAT_NAMES

可选兼容方案（不推荐） #

如果你的数据库版本支持STRING_SPLIT（SQL Server 2016及以上），也可以拆分字符串变量后判断，但性能比临时表方案差：

DECLARE @N VARCHAR(100)

WITH CAT_NAM AS (    
    SELECT ID, NAME
    FROM TABLE1    
    WHERE YEAR(DATE) = YEAR(GETDATE())    
)    
SELECT @N = STUFF((
    -- 不需要额外拼接引号，直接拼接原始值即可
    SELECT ',' + NAME
    FROM CAT_NAM    
    WHERE ID IN (20,23,25,30,37)   
    FOR XML PATH ('')    
),1,1,'')

SELECT 
    CASE 
        WHEN CAT_NAM_1 IN (SELECT value FROM STRING_SPLIT(@N, ',')) THEN CAT_NAM_1
        WHEN CAT_NAM_2 IN (SELECT value FROM STRING_SPLIT(@N, ',')) THEN CAT_NAM_2
        WHEN CAT_NAM_3 IN (SELECT value FROM STRING_SPLIT(@N, ',')) THEN CAT_NAM_3
        WHEN CAT_NAM_4 IN (SELECT value FROM STRING_SPLIT(@N, ',')) THEN CAT_NAM_4
    END AS CAT
FROM table2

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