使用 WHERE NOT 查詢和 NOT IN 使用子查詢

MySQL 版本：5.6.27

我正在使用以下兩個表

TABLE: oc_product
product_id      int(11)
model           varchar(64)
tax_class_id    int(11)

和

TABLE: oc_product_to_category
product_id      int(11)
category_id     int(11)

我想更改tax_class_idTABLE 上的一些產品oc_product。所以我SELECT之前正在執行一個查詢UPDATE

SELECT `product_id`, `model`, `tax_class_id` 
FROM `oc_product` 
WHERE 
NOT `tax_class_id` = 12 
AND
`product_id` 
NOT IN ( SELECT `product_id` 
       FROM  `oc_product_to_category` 
       WHERE  `category_id` 
       IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433) 
       )

不幸的是，結果並不像預期的那樣。

我想要的只是product_id從表oc_product中選擇所有tax_class_id不是 12 並且product_id在以下子查詢的結果中不存在的。

SELECT `product_id` 
FROM  `oc_product_to_category` 
WHERE  `category_id` 
IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)

我錯過了什麼？請指教！

您的意外結果可能源於product_id被分配到oc_product_to_category表中的多個類別。如果產品在類別 1 和類別 73 中，它仍將由內部查詢返回，因此從外部查詢中排除。

生成一組簡單的測試數據

INTO oc_product INSERT (product_id,  model, tax_class_id) VALUES
(1, 'model5', 10),
(2, 'AA', 5),
(3, '12', 8),
(4, '14', 12),
(5, 'xx', 12)

INTO oc_product_to_category INSERT (product_id, category_id) VALUES
(1, 1), -- product_id is in a category that is not in that list
(1, 73), -- product_id is STILL in a category that is not in that list; as well as a value in that list
(2, 73), -- product_id is only in categories in that list
(3, 17), -- product_id is only in categories not in that list
(4, 33), -- product_id is only in categories not on the category list, is excluded because of tax category
(5, 73), -- product_id is only in categories in the list, is excluded because of tax category

（我可能有向後插入查詢的語法；））

我相信您目前的查詢會返回

(2, 'AA', 5),

你可能想要類似的東西

SELECT 
   op.`product_id`,
   op.`model`,
   op.`tax_class_id` 
FROM
   oc_product AS op
 LEFT JOIN
   (SELECT DISTINCT p2c.product_id
    FROM oc_product_to_category as p2c
    WHERE
        p2c.category_id IN (73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
   ) as OnlyProductsBelongingToTheseIncluded
 ON op.product_id = OnlyProductsBelongingToTheseIncluded.product_id
WHERE 
   ( op.tax_class_id <> 12 ) AND
   OnlyProductsBelongingToTheseIncluded.product_id IS NOT NULL

這應該返回

(1, 'model5', 10),
(2, 'AA', 5)

3 被排除，因為它只存在於其他組中；由於稅碼，4 和 5 被排除在外

將最後一行更改為 AllProductsBelongingToTheseExcluded.product_id IS NULL（以及適當的子查詢別名）應該只返回(3, '12', 8)數據點

作為替代方案，您可以嘗試在 oc_product 上進行左連接。

SELECT a.`product_id`, a.`model`, a.`tax_class_id` 
FROM `oc_product` a
left join
( SELECT `product_id` 
       FROM  `oc_product_to_category` 
       WHERE  `category_id` 
       NOT IN ( 73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
) b
on a.product_id=b.product_id
WHERE 
a.`tax_class_id` != 12 
and b.product_id is null

因此，使用左連接，您可以從 oc_product 中獲取每一行。您將它加入 product_id 到您的子查詢。它將顯示來自 oc_product 的所有內容，但僅當 product_id 匹配時才會顯示子查詢中的行，當沒有匹配時將 b.product_id 留空。

然後，您可以僅過濾 b.product_id 空值並獲得您正在尋找的 product_id。

這種方法有時也會更快，具體取決於您的表和索引。

如果結果仍然不匹配，您可能需要考慮數據本身有什麼問題。查詢邏輯很好。

引用自：https://dba.stackexchange.com/questions/119959