Sql拆分逗号分隔的数据，并与其他表进行检查

Question

我有一个包含两个字段的表，一个用于标记，另一个用于ProspectID

DECLARE @Filter NVARCHAR(251) ='30,40'
declare @temp table
(
TagID       NVARCHAR(MAX),
ProspectID  INT
)
INSERT INTO @temp(TAGID,ProspectID)

VALUES 
('20,30,40' ,1),
('30,50' ,2),
('20,30,40' ,3),
('60,70' ,4),
('30' ,5)

需要返回30包含prospectID，40根据我的示例包含

输出I需要

ProspectID  
1
3
5

Answer 1

这是带有余数的关系划分的问题，有很多解决方案。我将介绍一个常见的。

您可以使用STRING_SPLIT来分解您的价值观：

declare @temp table
(
TagID       NVARCHAR(MAX),
ProspectID  INT
)
INSERT INTO @temp(TAGID,ProspectID)

VALUES 
('20,30,40' ,1),
('30,50' ,2),
('20,30,40' ,3),
('60,70' ,4),
('30' ,5)
DECLARE @Filter NVARCHAR(251) ='30,40'
SELECT
t.ProspectID
FROM @temp t
WHERE EXISTS (SELECT 1
FROM STRING_SPLIT(@Filter, ',') f
LEFT JOIN STRING_SPLIT(t.TagID, ',') t ON t.value = f.value
HAVING COUNT(t.value) = COUNT(*)  -- none missing
);

db<gt；小提琴

但是，您的模式设计是有缺陷的不要在一列或一个值中存储多条信息。相反，将它们存储在单独的行中。

因此，您将有一个表ProspectTag来存储每个组合(通过将字符串拆分为单独的行来获得(，并且@Filter也应该是一个表变量或表值参数。

declare @temp table
(
TagID       int,
ProspectID  int
);
INSERT INTO @temp (TagID, ProspectID)
VALUES
(20, 1),
(30, 1),
(40, 1),
(30, 2),
(50, 2),
(20, 3),
(30, 3),
(40, 3),
(60, 4),
(70, 4),
(30, 5);
DECLARE @Filter TABLE(value int PRIMARY KEY);
INSERT @Filter (value) VALUES
(30),
(40);
DECLARE @totalFilters int = (SELECT COUNT(*) FROM @Filter);
SELECT
t.ProspectID
FROM @temp t
JOIN @Filter f ON t.TagID = f.value
GROUP BY
t.ProspectID
HAVING COUNT(*) = @totalFilters;  -- none missing

db<gt；小提琴

Answer 2

使用以下查询

SELECT ProspectID
FROM @temp
WHERE TAGID LIKE '%' + @Filter + '%'
--WHERE TAGID LIKE '%' + LTRIM(RTRIM(@Filter)) + '%'
or TagID  in (PARSENAME(REPLACE(@Filter,',','.'),2) )--for ProspectID=5