SQL Server查询逻辑难题：如何筛选关联表中全匹配的ID数据

核心思路 #

先筛选出Table2中所有拆分后的ID都存在于Table1的记录，再关联Table1提取对应的人员信息。

实现代码 #

方法1：通过分组校验不存在ID的数量

-- 先筛选出符合条件的Table2记录，再关联Table1获取详情
SELECT 
    t1.id,
    t1.name,
    t1.salary,
    valid_t2.cid_addon
FROM (
    -- 筛选：当前CID的所有ID都存在于Table1的Table2记录
    SELECT 
        t2.CID,
        t2.cid_addon
    FROM Table2 t2
    CROSS APPLY STRING_SPLIT(t2.CID, ';') AS split_ids
    GROUP BY t2.CID, t2.cid_addon
    -- 统计不存在于Table1的ID数量，为0则说明全部有效
    HAVING COUNT(CASE WHEN NOT EXISTS (
        SELECT 1 FROM Table1 t1 WHERE t1.id = split_ids.value
    ) THEN 1 END) = 0
) valid_t2
-- 拆分有效CID，关联Table1获取人员数据
CROSS APPLY STRING_SPLIT(valid_t2.CID, ';') AS split_ids
JOIN Table1 t1 ON t1.id = split_ids.value

方法2：通过左连接匹配数校验

SELECT 
    t1.id,
    t1.name,
    t1.salary,
    t2.cid_addon
FROM Table2 t2
CROSS APPLY STRING_SPLIT(t2.CID, ';') AS split_ids
LEFT JOIN Table1 t1 ON t1.id = split_ids.value
GROUP BY t2.CID, t2.cid_addon, t1.id, t1.name, t1.salary
-- 匹配成功的ID数量等于拆分出的总ID数量，说明全部有效
HAVING COUNT(t1.id) = COUNT(split_ids.value)

逻辑说明 #

• STRING_SPLIT(t2.CID, ';')：将分号分隔的CID字段拆分为单个ID的行数据
• 分组校验逻辑：通过统计无效ID（不在Table1中的ID）的数量，或对比匹配成功的ID数量与总拆分ID数量，确保当前Table2记录的所有ID都存在于Table1
• 最终关联Table1，输出符合条件的人员姓名和薪资

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