从另一列包含集合中所有值的记录中查找唯一的列值

使用 group by 和 having :

select id
from t
where word in ('a', 'dog', 'has')
group by id
having count(*) = 3;  - the number of words in the list

如果您使用的是SQL 2016或更高版本,这似乎是一个很好的解决方案:

--Load test data
DECLARE @tbl as TABLE (id int, word varchar(40))
INSERT INTO @tbl
(id, word)
VALUES
(1, 'a'),(1,'dog'),(1,'cat'),(1,'has'),(1,'a'),(2,'two'),(2,'three'),(2,'four'),(2,'five'),(3,'black'),(3,'red')

--ACTUAL SOLUTION STARTS HERE
DECLARE @srch as VARCHAR(200)
SET @srch = 'a,dog,has'

SELECT id 
FROM @tbl
WHERE word IN (SELECT value FROM STRING_SPLIT(@srch,','))
GROUP BY id
HAVING COUNT(DISTINCT word) = (SELECT COUNT(DISTINCT value) FROM STRING_SPLIT(@srch,','));