选择在 IN() 列表中未找到的那些 [英] SELECT those not found in IN() list

问题描述

我有一张超过 1000 张桌子的桌子(例如客户).

I have a table with over 1000 tables (e.g Customers).

我有一个查询需要已知客户列表的详细信息(例如通过 CustomerID - 1,79,14,100,123)

I have a query requiring details of a known list of customers (e.g by CustomerID - 1,79,14,100,123)

IN() 函数是我想用于查询的函数.

The IN() function is what I would like to use for the query.

我知道要找到与列表匹配的客户，我会写:

I know to find customers that match the list, I would write:

SELECT * FROM Customers
WHERE CustomerID IN (1,79,14,100,123)

要找到不在列表中的那些，我会写

To find those that are not in the list, I would write

SELECT * FROM Customers
WHERE CustomerID NOT IN (1,79,14,100,123)

问题

如何找到未返回或未从列表中找到匹配项的客户列表?

假设客户表只有 (1,79,100).那么这意味着 14 和 123 将不匹配.我如何找到那些没有找到匹配的值.

Suppose the Customers table only has (1,79,100). Then it would mean 14 and 123 will not be matched. How do I find those values that do not find a match.

我在我的例子中进行了简化.我的项目列表有超过 300 个 ID，因此使用 WHERE 条件和一长串 OR 会很麻烦/笨拙.我想过结合 self LEFT JOIN 并确定 NULL 配对值，即 14 和 123

I was simplifying in my example. My list of items has over 300 IDs, so using WHERE condition with a long list of OR would be cumbersome/clumsy. I have thought of combining with self LEFT JOIN and identifying the NULL paired values, which would be 14 and 123

有没有更优雅的方法?

推荐答案

例如可以使用派生表或临时表来保存 CustomerId 的列表，然后找到与 除外.

You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT.

下面使用表值构造函数作为派生表(与 SQL Server 2008+ 兼容)

The below uses a table value constructor as a derived table (compatible with SQL Server 2008+)

SELECT CustomerId
FROM   (VALUES(1),
              (79),
              (14),
              (100),
              (123)) V(CustomerId)
EXCEPT
SELECT CustomerId
FROM   Customers 

这篇关于选择在 IN() 列表中未找到的那些的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！