单条SQL查询中LIKE与NOT LIKE组合使用结果异常问题

我尝试在单条SQL查询中同时使用LIKE和NOT LIKE，但结果不符合预期——本应仅返回1条记录，实际却返回了所有记录。

原SQL语句 #

USE SEAview
SELECT 
  ACCOUNT_NUMBER
, SITE_NUMBER
, ACCOUNT_SITE
, DOING_BUSINESS_AS_NAME
, ADDRESS
, CITY
, STATE
, ZIP_CODE
, LICENSE_CD
, BUSINESS_ACT_ID
INTO CURRENT_TAVERNS_TRUE
FROM CURRENT_TAVERNS
WHERE 
--
--LICENSE CD FOR LIKES AND DISLIKES
--
((LICENSE_CD LIKE '%1050%' OR
  LICENSE_CD LIKE '%1470%' OR
  LICENSE_CD LIKE '%1471%' OR
  LICENSE_CD LIKE '%1472%' OR
  LICENSE_CD LIKE '%1474%' OR
  LICENSE_CD LIKE '%1476%' OR
  LICENSE_CD LIKE '%1477%' OR
  LICENSE_CD LIKE '%1781%') AND
 --
-- NOT LIKES
--
 (LICENSE_CD NOT LIKE '%1003%' OR
  LICENSE_CD NOT LIKE '%1006%' OR
  LICENSE_CD NOT LIKE '%1007%' OR
  LICENSE_CD NOT LIKE '%1009%' OR
  LICENSE_CD NOT LIKE '%1010%' OR
  LICENSE_CD NOT LIKE '%1023%' OR
  LICENSE_CD NOT LIKE '%1032%' OR
  LICENSE_CD NOT LIKE '%1039%' OR
  LICENSE_CD NOT LIKE '%1058%' OR
  LICENSE_CD NOT LIKE '%1070%' OR
  LICENSE_CD NOT LIKE '%1075%' OR
  LICENSE_CD NOT LIKE '%1082%' OR
  LICENSE_CD NOT LIKE '%1253%' OR
  LICENSE_CD NOT LIKE '%1275%' OR
  LICENSE_CD NOT LIKE '%1473%' OR
  LICENSE_CD NOT LIKE '%1475%' OR
  LICENSE_CD NOT LIKE '%1480%' OR
  LICENSE_CD NOT LIKE '%1481%' OR
  LICENSE_CD NOT LIKE '%1833%' OR
  LICENSE_CD NOT LIKE '%2101%' OR
  LICENSE_CD NOT LIKE '%3717%' OR
  LICENSE_CD NOT LIKE '%4404%' OR
  LICENSE_CD NOT LIKE '%4405%' OR
  LICENSE_CD NOT LIKE '%4406%' OR
  LICENSE_CD NOT LIKE '%4408%' OR
  LICENSE_CD NOT LIKE '%4409%' OR
  LICENSE_CD NOT LIKE '%7002%' OR
  LICENSE_CD NOT LIKE '%7003%' OR
  LICENSE_CD NOT LIKE '%7006%' OR
  LICENSE_CD NOT LIKE '%7008%' OR
  LICENSE_CD NOT LIKE '%7009%' OR
  LICENSE_CD NOT LIKE '%7013%' OR
  LICENSE_CD NOT LIKE '%7020%' OR
  LICENSE_CD NOT LIKE '%7026%' OR
  LICENSE_CD NOT LIKE '%7029%' OR
  LICENSE_CD NOT LIKE '%7030%' OR
  LICENSE_CD NOT LIKE '%7032%' OR
  LICENSE_CD NOT LIKE '%7034%' OR
  LICENSE_CD NOT LIKE '%7035%' OR
  LICENSE_CD NOT LIKE '%7036%' OR
  LICENSE_CD NOT LIKE '%8340%' OR
  LICENSE_CD NOT LIKE '%8342%' OR
  LICENSE_CD NOT LIKE '%8343%' OR
  LICENSE_CD NOT LIKE '%8344%' OR
  LICENSE_CD NOT LIKE '%8345%' OR
  LICENSE_CD NOT LIKE '%8604%' OR
  LICENSE_CD NOT LIKE '%1594%' OR
  LICENSE_CD NOT LIKE '%1625%' OR
  LICENSE_CD NOT LIKE '%1683%' OR
  LICENSE_CD NOT LIKE '%1473%' OR
  LICENSE_CD NOT LIKE '%1475%' OR
  LICENSE_CD NOT LIKE '%1480%' OR
  LICENSE_CD NOT LIKE '%1481%' OR
  LICENSE_CD NOT LIKE '%1594%' OR
  LICENSE_CD NOT LIKE '%1625%' OR
  LICENSE_CD NOT LIKE '%1683%' OR
  LICENSE_CD NOT LIKE '%1781%')) 
GROUP BY
  ACCOUNT_NUMBER
, SITE_NUMBER
, ACCOUNT_SITE
, DOING_BUSINESS_AS_NAME
, ADDRESS
, CITY
, STATE
, ZIP_CODE
, LICENSE_CD
, BUSINESS_ACT_ID
SELECT * FROM CURRENT_TAVERNS_TRUE

测试数据 #

LICENSE_CD
1006,1470,1781
1006,1470
1006,1470
1006,1050,1470,1477
1006,1470,1477
1006,1470
1006,1470,1477
1006,1470
1006,1470
1470,1472

问题原因 #

核心错误在NOT LIKE部分的逻辑运算符：你用了OR，但实际需要的是AND。

用OR的话，只要某条记录的LICENSE_CD不包含其中任意一个排除值，整个NOT LIKE条件就会成立。比如一条记录包含1006，但它不包含1003，那么LICENSE_CD NOT LIKE '%1003%'为真，整个OR条件就会返回真，这条记录就会被选中——这显然和你要排除所有包含这些值的记录的需求不符。

另外原条件里还重复了多个排除项（比如%1473%出现两次），可以清理掉，但不影响逻辑。

修正后的SQL #

把NOT LIKE部分的所有OR替换成AND，确保记录不包含任何一个排除的LICENSE_CD值：

USE SEAview
SELECT 
  ACCOUNT_NUMBER
, SITE_NUMBER
, ACCOUNT_SITE
, DOING_BUSINESS_AS_NAME
, ADDRESS
, CITY
, STATE
, ZIP_CODE
, LICENSE_CD
, BUSINESS_ACT_ID
INTO CURRENT_TAVERNS_TRUE
FROM CURRENT_TAVERNS
WHERE 
-- 匹配允许的LICENSE_CD
(LICENSE_CD LIKE '%1050%' OR
  LICENSE_CD LIKE '%1470%' OR
  LICENSE_CD LIKE '%1471%' OR
  LICENSE_CD LIKE '%1472%' OR
  LICENSE_CD LIKE '%1474%' OR
  LICENSE_CD LIKE '%1476%' OR
  LICENSE_CD LIKE '%1477%' OR
  LICENSE_CD LIKE '%1781%') 
AND
-- 排除所有包含以下值的记录
(LICENSE_CD NOT LIKE '%1003%' AND
  LICENSE_CD NOT LIKE '%1006%' AND
  LICENSE_CD NOT LIKE '%1007%' AND
  LICENSE_CD NOT LIKE '%1009%' AND
  LICENSE_CD NOT LIKE '%1010%' AND
  LICENSE_CD NOT LIKE '%1023%' AND
  LICENSE_CD NOT LIKE '%1032%' AND
  LICENSE_CD NOT LIKE '%1039%' AND
  LICENSE_CD NOT LIKE '%1058%' AND
  LICENSE_CD NOT LIKE '%1070%' AND
  LICENSE_CD NOT LIKE '%1075%' AND
  LICENSE_CD NOT LIKE '%1082%' AND
  LICENSE_CD NOT LIKE '%1253%' AND
  LICENSE_CD NOT LIKE '%1275%' AND
  LICENSE_CD NOT LIKE '%1473%' AND
  LICENSE_CD NOT LIKE '%1475%' AND
  LICENSE_CD NOT LIKE '%1480%' AND
  LICENSE_CD NOT LIKE '%1481%' AND
  LICENSE_CD NOT LIKE '%1833%' AND
  LICENSE_CD NOT LIKE '%2101%' AND
  LICENSE_CD NOT LIKE '%3717%' AND
  LICENSE_CD NOT LIKE '%4404%' AND
  LICENSE_CD NOT LIKE '%4405%' AND
  LICENSE_CD NOT LIKE '%4406%' AND
  LICENSE_CD NOT LIKE '%4408%' AND
  LICENSE_CD NOT LIKE '%4409%' AND
  LICENSE_CD NOT LIKE '%7002%' AND
  LICENSE_CD NOT LIKE '%7003%' AND
  LICENSE_CD NOT LIKE '%7006%' AND
  LICENSE_CD NOT LIKE '%7008%' AND
  LICENSE_CD NOT LIKE '%7009%' AND
  LICENSE_CD NOT LIKE '%7013%' AND
  LICENSE_CD NOT LIKE '%7020%' AND
  LICENSE_CD NOT LIKE '%7026%' AND
  LICENSE_CD NOT LIKE '%7029%' AND
  LICENSE_CD NOT LIKE '%7030%' AND
  LICENSE_CD NOT LIKE '%7032%' AND
  LICENSE_CD NOT LIKE '%7034%' AND
  LICENSE_CD NOT LIKE '%7035%' AND
  LICENSE_CD NOT LIKE '%7036%' AND
  LICENSE_CD NOT LIKE '%8340%' AND
  LICENSE_CD NOT LIKE '%8342%' AND
  LICENSE_CD NOT LIKE '%8343%' AND
  LICENSE_CD NOT LIKE '%8344%' AND
  LICENSE_CD NOT LIKE '%8345%' AND
  LICENSE_CD NOT LIKE '%8604%' AND
  LICENSE_CD NOT LIKE '%1594%' AND
  LICENSE_CD NOT LIKE '%1625%' AND
  LICENSE_CD NOT LIKE '%1683%' AND
  LICENSE_CD NOT LIKE '%1781%')
GROUP BY
  ACCOUNT_NUMBER
, SITE_NUMBER
, ACCOUNT_SITE
, DOING_BUSINESS_AS_NAME
, ADDRESS
, CITY
, STATE
, ZIP_CODE
, LICENSE_CD
, BUSINESS_ACT_ID
SELECT * FROM CURRENT_TAVERNS_TRUE

修正后，只有最后一条1470,1472的记录会被选中，符合预期。

内容的提问来源于stack exchange，提问作者Tim Browne