请帮我写一个条件为'NOT LIKE IN‘的sql查询
Select * from Table1 where EmpPU NOT Like IN ('%CSE%', '%ECE%', '%EEE%')
获取错误。
发布于 2012-02-22 18:58:16
您不能将like和in组合在一起。下面的语句可以完成这项工作:
Select * from Table1
where EmpPU NOT Like '%CSE%'
AND EmpPU NOT Like '%ECE%'
AND EmpPU NOT Like '%EEE%'
发布于 2012-02-22 18:58:25
这是因为您将两种语法混合在一起。
如果您始终具有这三个值,则可以仅对三个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)
发布于 2012-02-22 19:09:58
你不能组合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%'
https://stackoverflow.com/questions/9393439
复制相似问题