不包括使用和不使用记录的SQL

Question

下面是我正在处理的一个查询：

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND ( poe.[TBL_COMPANY] <> '011'
              AND poe.[TBL_DIVISION_1] <> '11'
            )

我想要返回的是公司=‘011’/除法_1‘= '11’中所有而不是的记录。

我原以为合并()中的公司/部门会达到这个目的，但它没有。它消除了所有公司的“011”记录和所有部门的“11”记录。

然而，当我这样做时：

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND NOT ( poe.[TBL_COMPANY] = '011'
              AND poe.[TBL_DIVISION_1] = '11'
            )

它似乎得出了正确的结果。为什么会这样呢？

Answer 1

这是一个布尔逻辑问题：

NOT (A and B) <=> NOT A  OR NOT B

即：

NOT ( poe.[TBL_COMPANY] = '011' AND poe.[TBL_DIVISION_1] = '11')

相当于：

( poe.[TBL_COMPANY] <> '011' OR poe.[TBL_DIVISION_1] <> '11')

阅读：

逻辑

Answer 2

我认为与NOT相等的人将使用OR，如下所示：

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND ( poe.[TBL_COMPANY] <> '011'
              OR poe.[TBL_DIVISION_1] <> '11'
            )

Answer 3

where x and (y and z)

是相同的

x and y and z

然而，

where x and not (y and z)

是你想要的，而且很管用。

试试这个：

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND NOT ( poe.[TBL_COMPANY] = '011'
                  AND poe.[TBL_DIVISION_1] = '11'
                )