求适配多行Table B的SQL代码：统计Table A列值的存在情况

嘿，针对你这个需要检查Table A各列值是否存在于多行Table B任意列的需求，我给你调整了SQL代码，不用依赖变量，直接用集合操作来处理多行场景：

-- 先把Table B的所有列数据转成单行一列的集合，方便匹配
WITH B_Unpivoted AS (
    SELECT Value
    FROM TableB
    UNPIVOT (
        Value FOR Columns IN (N1, N2, N3, N4, N5, N6, N7, N8)
    ) AS up
)
-- 然后检查Table A的每个列值是否在上面的集合中
SELECT
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N1) THEN 1 ELSE 0 END AS N1_Exists,
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N2) THEN 1 ELSE 0 END AS N2_Exists,
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N3) THEN 1 ELSE 0 END AS N3_Exists
FROM TableA A;

代码解释： #

1. CTE部分（B_Unpivoted）：用UNPIVOT把Table B的8列数据转换成一列（名为Value），这样不管Table B有多少行，所有列的数值都被放到一个集合里，方便后续统一判断。
2. 主查询部分：对Table A的每一列（N1、N2、N3），分别检查该值是否存在于B_Unpivoted的Value集合中，存在返回1，不存在返回0。

如果你的SQL版本不支持UNPIVOT（比如一些老版本），也可以用UNION ALL来手动拼接所有列：

WITH B_Unpivoted AS (
    SELECT N1 AS Value FROM TableB
    UNION ALL SELECT N2 FROM TableB
    UNION ALL SELECT N3 FROM TableB
    UNION ALL SELECT N4 FROM TableB
    UNION ALL SELECT N5 FROM TableB
    UNION ALL SELECT N6 FROM TableB
    UNION ALL SELECT N7 FROM TableB
    UNION ALL SELECT N8 FROM TableB
)
SELECT
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N1) THEN 1 ELSE 0 END AS N1_Exists,
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N2) THEN 1 ELSE 0 END AS N2_Exists,
    CASE WHEN EXISTS(SELECT 1 FROM B_Unpivoted WHERE Value = A.N3) THEN 1 ELSE 0 END AS N3_Exists
FROM TableA A;

这两种写法都能完美适配Table B有多行的场景，而且比你原来用变量的方式更灵活、更符合SQL的集合操作思想，不用硬编码变量和判断条件。

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