带有 NOT LIKE IN 的 SQL 查询

你可以试试这个

Select * from Table1 where
EmpPU NOT Like '%CSE%'
AND EmpPU NOT Like '%ECE%'
AND EmpPU NOT Like '%EEE%'

你不能把 like 和 in 组合在一起。下面的语句可以做到这一点:

Select * from Table1
where EmpPU NOT Like '%CSE%'
AND EmpPU NOT Like '%ECE%'
AND EmpPU NOT Like '%EEE%'

那是因为你把两种语法混在一起了。

如果总是恰好具有这三个值，那么可以只对三个 LIKE 表达式的结果进行 AND 运算。

SELECT
*
FROM
Table1
WHERE
EmpPU NOT LIKE '%CSE%'
AND EmpPU NOT LIKE '%ECE%'
AND EmpPU NOT LIKE '%EEE%'

如果需要对“任意数量”的值执行此操作，则可以将这些值放入表中并执行连接。

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

或者..。

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)

或者你可以这样做:

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
)

不能将 LIKE 和 IN 组合在一起

你可以:

select * from Table1
where EmpPU not in ('%CSE%', '%ECE%', '%EEE%')

但是您不会从% 通配符中获益

如果需要% ，唯一的选择是:

Select * from Table1
where EmpPU not like '%CSE%' and  EmpPU not like '%ECE%' and EmpPU not like '%EEE%'

如果你有一组单词，你想包括/排除搜索从一个特定的列。您可能需要使用 mysql 的正则表达式函数。

从列中排除一组单词:

SELECT
*
FROM
Table1
WHERE
EmpPU NOT REGEXP 'CSE|ECE|EEE';

从列中搜索一组单词:

SELECT
*
FROM
Table1
WHERE
EmpPU REGEXP 'CSE|ECE|EEE';

密码如下:

Select * from Table1 where
(EmpPU NOT Like '%CSE%'
OR EmpPU NOT Like '%ECE%'
OR EmpPU NOT Like '%EEE%')

或使用 EXCEPT:

    SELECT * FROM Table1
EXCEPT
SELECT * FROM Table1
WHERE EmpPU LIKE '%CSE%'
OR EmpPU LIKE '%ECE%'
OR EmpPU LIKE '%EEE%'