查找所有ID均未存在于另一表中的分组的SQL实现

现有两张临时表：

#Groups表 #

#Test表 #

需求 #

筛选出所有ID均未出现在#Test表中的分组，预期结果为Group_ids为3,4的记录。

建表及插入数据语句 #

Create table #groups(group_ids varchar(200))
insert into #groups values ('1,2', '3,4', '5,6')

create table #test(id int, test_date datetime)
insert into #test values (1, '1/1/2022'), (5, '2/1/2022')

尝试的错误SQL #

select *
from #groups g
cross apply
( select 
count(t.test_date) over (partition by t.id) as 'test_ids_count',
count(g.value) over (partition by g.value) as 'ids_total'
from string_split(g.group_ids) g
left outer join #test t on g.value = t.id
where g.id is null)sy
where sy.test_ids_count = sy.ids_total

方法一：用NOT EXISTS验证分组无匹配ID #

通过CROSS APPLY拆分分组ID，检查该分组下是否存在任何ID匹配#Test表，若不存在则保留：

SELECT g.group_ids
FROM #groups g
WHERE NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(g.group_ids, ',') s
    JOIN #test t ON CAST(s.value AS INT) = t.id
)

方法二：分组统计匹配数，筛选无匹配的分组 #

拆分ID后统计每个分组在#Test表中的匹配数量，若匹配数为0则保留：

SELECT g.group_ids
FROM #groups g
CROSS APPLY STRING_SPLIT(g.group_ids, ',') s
LEFT JOIN #test t ON CAST(s.value AS INT) = t.id
GROUP BY g.group_ids
HAVING COUNT(t.id) = 0

错误原因说明 #

原SQL存在两处核心问题：

1. STRING_SPLIT调用缺少分隔符参数，无法正确拆分ID字符串；
2. 子查询误用窗口函数统计，逻辑混乱，无法准确判断分组内是否存在匹配的ID。

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