带有 NOT LIKE IN 的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9393439/
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 Query with NOT LIKE IN
提问by venkat
Please help me to write a sql query with the conditions as 'NOT LIKE IN'
请帮我写一个条件为“NOT LIKE IN”的sql查询
Select * from Table1 where EmpPU NOT Like IN ('%CSE%', '%ECE%', '%EEE%')
Getting error.
获取错误。
回答by Paddy
You cannot combine like and in. The statement below would do the job though:
你不能将 like 和 in 结合起来。不过，下面的语句可以完成这项工作：
Select * from Table1
where EmpPU NOT Like '%CSE%'
AND EmpPU NOT Like '%ECE%'
AND EmpPU NOT Like '%EEE%'
回答by MatBailie
That's because you're mixing two syntax together.
那是因为您将两种语法混合在一起。
If you always have exactly those three values, you can just AND the results of three LIKE expressions.
如果你总是有这三个值，你可以只对三个 LIKE 表达式的结果进行 AND 运算。
SELECT
*
FROM
Table1
WHERE
EmpPU NOT LIKE '%CSE%'
AND EmpPU NOT LIKE '%ECE%'
AND EmpPU NOT LIKE '%EEE%'
If you need to do it for "any number" of values, you can put the values into a table and do a join.
如果您需要为“任意数量”的值执行此操作，您可以将这些值放入一个表中并进行连接。
WITH
myData
AS
(
SELECT '%CSE%' AS match
UNION ALL SELECT '%ECE%' AS match
UNION ALL SELECT '%EEE%' AS match
)
SELECT
*
FROM
Table1
LEFT JOIN
myData
ON Table1.EmpPU LIKE myData.match
WHERE
myData.match IS NULL
OR...
或者...
WITH
myData
AS
(
SELECT '%CSE%' AS match
UNION ALL SELECT '%ECE%' AS match
UNION ALL SELECT '%EEE%' AS match
)
SELECT
*
FROM
Table1
WHERE
NOT EXISTS (SELECT * FROM myData WHERE Table1.EmpPU LIKE match)
回答by shashankqv
If you have set of words which you want to include/exclude in search from a particular column. You may want to use regular expression function of mysql.
如果您有一组要在特定列的搜索中包含/排除的词。您可能想使用 mysql 的正则表达式功能。
Exclude set of words from a column :
从列中排除一组单词：
SELECT
*
FROM
Table1
WHERE
EmpPU NOT REGEXP 'CSE|ECE|EEE';
Search set of words from a column :
从列中搜索一组词：
SELECT
*
FROM
Table1
WHERE
EmpPU REGEXP 'CSE|ECE|EEE';
回答by Diego
you cant combine LIKE and IN
你不能结合 LIKE 和 IN
you can do:
你可以做：
select * from Table1
where EmpPU not in ('%CSE%', '%ECE%', '%EEE%')
but you wont benefit from the % wildcard
但您不会从 % 通配符中受益
if you need the % the only option is:
如果您需要 % 唯一的选择是：
Select * from Table1
where EmpPU not like '%CSE%' and EmpPU not like '%ECE%' and EmpPU not like '%EEE%'
回答by Arion
Or you can do it like this:
或者你可以这样做：
SELECT
*
FROM
Table1
WHERE NOT EXISTS
(
SELECT
NULL
FROM
(
SELECT '%CSE%' AS column1 UNION ALL
SELECT '%ECE%' UNION ALL
SELECT '%EEE%'
) AS tbl
WHERE Table1.EmpPU LIKE tbl.column1
)
回答by Zyku
you can try this
你可以试试这个
Select * from Table1 where
EmpPU NOT Like '%CSE%'
AND EmpPU NOT Like '%ECE%'
AND EmpPU NOT Like '%EEE%'
回答by Albatros
Code is as below:
代码如下：
Select * from Table1 where
(EmpPU NOT Like '%CSE%'
OR EmpPU NOT Like '%ECE%'
OR EmpPU NOT Like '%EEE%')
回答by Bruno Manuel Rosas Marques
Or use EXCEPT:
或使用EXCEPT：
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table1
WHERE EmpPU LIKE '%CSE%'
OR EmpPU LIKE '%ECE%'
OR EmpPU LIKE '%EEE%'
