查询:查找不属于值列表的行 [英] Query: find rows that do not belong to a list of values

问题描述

让我们考虑一下我有一个表Tab"，它有一列Col"

Lets consider I have a table 'Tab' which has a column 'Col'

表格'Tab'有这个数据 -

The table 'Tab' has this data -

Col
1
2
3
4
5

如果我有一组值 (2,3,6,7).我可以通过调用查询来查询表和列表中存在的值

If I have a set of values (2,3,6,7). I can query the values that are present in the table and the list by suing the query

Select Col from Tab where col IN (2,3,6,7)

但是，如果我想返回表中不存在的列表中的值，即在这种情况下仅返回 (6,7).我应该使用什么查询?

But, if I want to return the values in the list that are not present in the table i.e. only (6,7) in this case. What query should I use?

推荐答案

我认为问题在于您试图从语句中找到值.您需要做的是将您的 in 语句变成表格，然后您可以确定哪些值不同.

The problem I believe is that your trying to find values from you in statement. What you need to do is turn your in statement into a table and then you can determine which values are different.

create table #temp
(
value int
)

insert into #temp values 1
insert into #temp values 2
insert into #temp values 3
insert into #temp values 4

select
 id
from
 #temp
where
 not exists (select 1 from Tab where Col = id)

更好的替代方法是创建一个表值函数，将逗号分隔的字符串转换为表.我手头没有任何代码，但在 Google 上应该很容易找到.在这种情况下，您只需使用以下语法.

A better alternative would be to create a table-valued function to turn your comma-delimited string into a table. I don't have any code handy, but it should be easy to find on Google. In that case you would only need to use the syntax below.

select
 id
from
 dbo.SplitStringToTable('2,3,6,7')
where
 not exists (select 1 from Tab where Col = id)

希望能帮到你

这篇关于查询:查找不属于值列表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！