代码之家 › 专栏 › 技术社区 › Brandon

从SQL中选择列与字符串数组中的某些内容不匹配的行?

tsql sql-server-2005 sql-server sql

Brandon · 技术社区 · 14 年前

假设我有一张桌子, Product ,列名为 ProductName ,值如下:

Lawnmower
Weedwacker
Backhoe
Gas Can
Batmobile

Lawnmower
Weedwacker
Batmobile

在我现实生活中的问题,有成千上万的记录,和成千上万的排除。在sqlstudio管理器中,如何构造一个类似于下面的伪代码的查询 Backhoe 和 Gas Can 结果如何?:

declare @excludedProductNames varchar(MAX) =
'Lawnmower
Weedwacker
Batmobile'

SELECT ProductName FROM Product
WHERE ProductName isn't in the list of @excludedProductNames

这只是一个一次性的报告,所以我一点也不关心性能。

3 回复 | 直到 14 年前

tenfour 14 年前

我认为最好使用一些文本编辑器技巧来实现这一点。用替换换行符 ', ' select * from product where ProductName not in ('...', '...') 查询。

OMG Ponies 14 年前

第一件事是将这些词放入SSMS—您可以使用UNION ALL构造派生表:

SELECT 'Lawnmower' AS word
UNION ALL
SELECT 'Weedwacker'
UNION ALL
SELECT 'Batmobile'

这将返回一个表,其中有一列名为“word”:

word
--------
Lawnmower
Weedwacker
Batmobile

警告

您需要对数据中的任何单引号进行转义。IE:O'Brian需要改成O'Brian——只要把单引号加倍就可以了。

现在,真正的问题是。。。

使用NOT IN

一些数据库限制了in中子句的数量,大约在数千个IIRC中 NOT EXISTS 或 LEFT JOIN/IS NULL

SELECT p.*
  FROM PRODUCT p
 WHERE p.productname NOT IN (SELECT 'Lawnmower' AS word
                             UNION ALL
                             SELECT 'Weedwacker'
                             UNION ALL
                             SELECT 'Batmobile'
                             ...)

使用不存在

SELECT p.*
  FROM PRODUCT p
 WHERE NOT EXISTS (SELECT NULL
                     FROM (SELECT 'Lawnmower' AS word
                           UNION ALL
                           SELECT 'Weedwacker'
                           UNION ALL
                           SELECT 'Batmobile'
                           ...) x
                   WHERE x.word = p.productname)

使用LEFT JOIN/IS NULL

   SELECT p.*
     FROM PRODUCT p
LEFT JOIN (SELECT 'Lawnmower' AS word
           UNION ALL
           SELECT 'Weedwacker'
           UNION ALL
           SELECT 'Batmobile'
           ...) x ON x.word = p.productname
    WHERE x.word IS NULL

哪个效率最高/最快?

NOT IN or NOT EXIST are the best choice .

krock 14 年前

-- create temp table #exclusions
select ProductName into #exclusions
from Product
where 1 = 2

# run a bunch of inserts
insert into #exclusions (ProductName) values ('LawnMower')
-- as many as needed...

# run your select
select * from Product
where ProductName not in (select Product from #exclusions)

drop table #exclusions

作为运行大量插件的替代方法,使用 bcp 将包含产品名称的csv文件上载到非临时表中。