SQL 从表中排除 LIKE 项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/430000/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Exclude LIKE items from table
提问by MaxGeek
I'm trying to figure out how to exclude items from a select statement from table A using an exclusion list from table B. The catch is that I'm excluding based on the prefix of a field.
我试图弄清楚如何使用表 B 中的排除列表从表 A 的选择语句中排除项目。问题是我根据字段的前缀进行排除。
So a field value maybe "FORD Muffler" and to exclude it from a basic query I would do:
所以一个字段值可能是“福特消声器”并将其从基本查询中排除，我会这样做：
SELECT FieldName
FROM TableName
WHERE UPPER(ColumnName) NOT LIKE 'FORD%'
But to use a list of values to exclude from a different tabel I would use a Subquery like:
但是要使用值列表从不同的表中排除，我将使用子查询，例如：
SELECT FieldName
FROM TableName
WHERE UPPER(ColumnName) NOT IN (Select FieldName2 FROM TableName2)
The problem is that it only excludes exact matches and not LIKE or Wildcards (%).
问题是它只排除完全匹配而不排除 LIKE 或通配符 (%)。
How can I accomplish this task? Redesigning the table isn't an option as it is an existing table in use.
我怎样才能完成这个任务？重新设计桌子不是一种选择，因为它是一个正在使用的现有桌子。
EDIT: Sorry I am using SQL Server (2005).
编辑：抱歉，我正在使用 SQL Server (2005)。
回答by Joel Coehoorn
I think this will do it:
我认为这会做到：
SELECT FieldName
FROM TableName
LEFT JOIN TableName2 ON UPPER(ColumnName) LIKE TableName2.FieldName2 + '%'
WHERE TableName2.FieldName2 IS NULL
回答by Matt Hamilton
Dunno how efficient this would be, but it should work:
不知道这会有多有效，但它应该有效：
SELECT FieldName
FROM TableName t1
WHERE NOT EXISTS (
SELECT *
FROM TableName2 t2
WHERE t1.FieldName LIKE t2.FieldName2 + '%'
)
回答by SquareCog
SELECT * FROM table_A
LEFT OUTER JOIN table_B
ON (locate(table_b.column, UPPER(table_a.column)) == 1)
WHERE table_b.column IS NULL
