代码之家  ›  专栏  ›  技术社区  ›  ziggy

postgresql的两个NOT-LIKE子句返回错误的结果

  •  0
  • ziggy  · 技术社区  · 6 年前

    我有一张88码的桌子。我正在编写一个简单的select语句,它不选择以 10 18

    select distinct pcl_mun from exemptions.modiv_parcels_2015 
    where (pcl_mun NOT LIKE '10%') or (pcl_mun NOT LIKE '18%')
    order by pcl_mun
    

    有人会假设这是可行的,但它会返回所有的结果

    "0233"
    "0242"
    "1001"
    "1002"
    "1003"
    "1004"
    "1005"
    "1006"
    "1012"
    "1013"
    "1014"
    "1015"
    "1018"
    "1019"
    "1020"
    "1024"
    "1025"
    "1401"
    "1402"
    "1403"
    "1406"
    "1407"
    "1408"
    "1409"
    "1412"
    "1413"
    "1414"
    "1415"
    "1418"
    "1419"
    "1420"
    "1421"
    "1422"
    "1423"
    "1424"
    "1425"
    "1426"
    "1427"
    "1428"
    "1429"
    "1431"
    "1432"
    "1433"
    "1434"
    "1435"
    "1436"
    "1437"
    "1438"
    "1439"
    "1601"
    "1609"
    "1611"
    "1613"
    "1615"
    "1801"
    "1802"
    "1803"
    "1807"
    "1815"
    "1904"
    "1906"
    "1908"
    "1909"
    "1911"
    "1912"
    "1916"
    "1918"
    "1919"
    "1922"
    "2101"
    "2102"
    "2103"
    "2105"
    "2106"
    "2107"
    "2108"
    "2110"
    "2111"
    "2112"
    "2114"
    "2115"
    "2116"
    "2117"
    "2119"
    "2120"
    "2121"
    "2122"
    "2123"
    

    如果我单独运行每个like子句,它们将返回正确的结果。我做错什么了?

    1 回复  |  直到 6 年前
        1
  •  2
  •   LukStorms    6 年前

    合并时 NOT LIKE 然后 AND OR

    select distinct pcl_mun 
    from exemptions.modiv_parcels_2015 
    where pcl_mun NOT LIKE '10%' 
      AND pcl_mun NOT LIKE '18%'
    order by pcl_mun
    

    这是一件合乎逻辑的事情,有时会让人困惑。

    简化示例:

    ('18' NOT LIKE '10%') OR ('18' NOT LIKE '18%')  --> true OR false --> true
    ('18' NOT LIKE '10%') AND ('18' NOT LIKE '18%')  --> true AND false --> FALSE
    
    ('14' NOT LIKE '10%') OR ('14' NOT LIKE '18%')  --> true OR true --> TRUE
    ('14' NOT LIKE '10%') AND ('14' NOT LIKE '18%')  --> true AND true --> TRUE
    

    因为否定两个词的组合是不同的 LIKE
    那么 或者

    NOT ('18' LIKE '10%' OR '18' LIKE '18%')  --> NOT(false OR true) --> FALSE
    NOT ('18' LIKE '10%' AND '18' LIKE '18%')  --> NOT(false AND true) --> true
    
    NOT ('14' LIKE '10%' OR '14' LIKE '18%')  --> NOT(false OR false) --> TRUE
    NOT ('14' LIKE '10%' AND '14' LIKE '18%')  --> NOT(false AND false) --> TRUE