T-SQL - 验证所有值是否都包含在选择语句的结果中

Question

假设我有一个函数，在其中传递一个逗号分隔的值列表。然后我有另一个返回表的函数。如何验证逗号分隔列表中的每个值是否包含在第二个函数的结果中？如果逗号分隔的列表包含的值不在第二个函数的结果中，则结果必须为 FALSE。

有效方案：

输入： A， B， C

第二个函数的结果：A、B、C、D

无效方案：

输入： A， B， C， D

第二个函数的结果：A、B、C

提前谢谢。

Answer 1

像这样：

DECLARE @input VARCHAR(100)='1,3,4';
WITH splittedInput AS
(
SELECT val.value('text()[1]','int') As theInt
FROM
(
SELECT CAST('<x>' + REPLACE(@input,',','</x><x>') + '</x>' AS XML) AS singleValue
) AS x
CROSS APPLY x.singleValue.nodes('/x') As y(val)
)
SELECT *
FROM splittedInput AS si 
LEFT JOIN (VALUES(1),(2),(3),(4)) AS t(x) ON t.x=si.theInt
WHERE t.x IS NULL;

单独运行此部分

SELECT val.value('text()[1]','int') As theInt
FROM
(
SELECT CAST('<x>' + REPLACE(@input,',','</x><x>') + '</x>' AS XML) AS singleValue
) AS x
CROSS APPLY x.singleValue.nodes('/x') As y(val)

您会看到这将返回逗号分隔的输入作为派生表。

如果您使用的是SQL Server 2016 +，则可以使用STRING_SPLIT()这使整个事情变得更加简单。

该示例使用VALUES创建模拟结果以返回 1、2、3 和 4。LEFT JOIN将返回所有结果值以及可连接的输入值。如果没有，它将NULL.在没有WHERE子句的情况下运行它以查看差异。

尝试向输入添加一个值，该值未包含在输出中，然后重试该脚本。

更新

有了这样的SELECT

SELECT CASE WHEN COUNT(*)>0 THEN 0 ELSE 1 END AS ResultIsValid

如果需要，您将获得一个标记有效性的0或1以返回此内容。

更新 2：使用STRING_SPLIT()

在 2016 版中，MS 引入了一些新的字符串方法，其中之一是STRING_SPIT().

我目前无法对此进行测试(需要SQL服务器2016 +)，但这应该可以工作

SELECT *
FROM STRING_SPLIT(@input,',') AS ss 
LEFT JOIN (VALUES(1),(2),(3),(4)) AS t(x) ON t.x=ss.value
WHERE t.x IS NULL;