从SPLIT_STRING查找表中不存在的值

Question

我有一个过程，它将根据表中的所有记录是否在逗号分隔的字符串中查找这些记录。

DECLARE @PMIDList NVARCHAR(max) = '31282781,31226541,30518502,30262330,30154192,29781323,29222269,28516439,28314698,27130328,26031546,25963431'

SELECT PMID 
FROM ADMIN_API_Pubs_Members 
INNER JOIN STRING_SPLIT(@PMIDList,',') ON value = dbo.ADMIN_API_Pubs_Members.PMID

但是，我想稍微改变一下，找出逗号分隔的字符串中的值，这些值不在表中。

我添加了

SELECT PMID 
FROM ADMIN_API_Pubs_Members 
INNER JOIN STRING_SPLIT(@PMIDList,',') ON value = dbo.ADMIN_API_Pubs_Members.PMID
WHERE ADMIN_API_Pubs_Members.PMID IS NULL

但是怎样才能让逗号字符串值显示为值呢？我是否需要将所有值放入一个临时表格中，然后执行联合操作来查找空记录？我使用的是SQL Server 2016。

Answer 1

这是您的查询

SELECT * FROM (
 SELECT t1.value, PMID 
 FROM  STRING_SPLIT(@PMIDList,',') t1
 LEFT JOIN ADMIN_API_Pubs_Members ON value = dbo.ADMIN_API_Pubs_Members.PMID
) t2 WHERE t2.PMID IS NULL

Answer 2

如果你想得到那些存在于逗号分隔值而不是存在于表中的值，你可以用这种方式来获取数据。

select '31282781' as ID into #temp union all 
select '31226541' as ID union all 
select '30518502' as ID union all 
select '30262330' as ID  

DECLARE @PMIDList NVARCHAR(max) = '31282781,31226541,30518502,30262330,30154192,29781323,29222269,28516439,28314698,27130328,26031546,25963431'

SELECT  value as CSVdata   
FROM   STRING_SPLIT(@PMIDList,',') t2 
left join #temp t  on t2.value = t.ID 
where t.ID is null 

输出：

 CSVdata
30154192
29781323
29222269
28516439
28314698
27130328
26031546
25963431

Answer 3

一种选择是使用NOT EXISTS ()

SELECT *
FROM   STRING_SPLIT(@PMIDList,',') v
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   ADMIN_API_Pubs_Members x
           WHERE  x.PMID  = v.VALUE
       )