SQL:选择属于排除类别的记录，这些记录仅属于排除类别

Question

我有一个有效的 SELECT 语句，并且在我的表上运行速度足够快(<0.01 秒在 50k+ 产品，3k+ 类别上)。但在我看来，它不是很优雅，希望听到任何让它变得更好的建议。

感兴趣的表有3个:

• 产品 - 关键产品 ID
• 类别 - 关键类别 ID
• products_tree - 链接表(类别包含很多产品，产品可以属于很多类别)

我有一个排除的类别 ID 列表 [例如1040,1050,1168]我想选择属于这些排除类别之一的所有产品 ID 仅当产品不属于另一个非排除类别

我的查询是这样的:

SELECT DISTINCT productID 
FROM products_tree 
WHERE 
  categoryID IN (1040,1050,1168) 
  AND productID NOT IN
    ( SELECT DISTINCT productID 
      FROM products_tree 
      WHERE 
      categoryID NOT IN (1040,1050,1168)
    );

Answer 1

我可以想到几种方法，每种方法的执行方式都不同，具体取决于索引和您的特定数据库实现。一些看起来可能很慢的东西可以以您可能无法想象的方式进行优化，因此值得尝试所有这些并比较执行计划以查看正在发生的事情...

注意 1:我使用 GROUP BY 而不是 DISTINCT，这是因为它允许优化器使用索引。我已经看到实现可以将 DISTINCT 转换为 GROUP BY，但是非常值得在第一个地方使用 GROUP BY 以确保。它还会让您考虑索引，这从来都不是坏事。

注意 2:像这样的一些查询需要一段时间来优化，因为优化器有很多选项可以评估。因此，通常值得将所有不同的选项编译到存储过程中，并比较这些存储过程的执行情况。这确保您比较的是实际查询时间，而不是不同的编译时间。

SELECT
   [tree].productID
FROM
   products_tree AS [tree]
WHERE
   [tree].productID IN (1040,1050,1168)
   AND NOT EXISTS (SELECT * FROM products_tree WHERE productID = [tree].productID AND categoryID NOT IN (1040,1050,1168)) 
GROUP BY
   [tree].productID

SELECT
   [tree].productID
FROM
   products_tree AS [tree]
LEFT OUTER JOIN
   (
      SELECT
         productID
      FROM
         product_tree
      WHERE
         productID NOT IN (1040,1050,1168)
      GROUP BY
         productID
    )
    AS [ok_products]
       ON [ok_products].productID = [tree].productID
WHERE
   [tree].productID IN (1040,1050,1168)
   AND [ok_products].productID IS NULL 
GROUP BY
   [tree].productID

SELECT
   [tree].productID
FROM
   products_tree AS [tree]
GROUP BY
   [tree].productID
HAVING
       MAX(CASE WHEN [tree].productID     IN (1040,1050,1168) THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN [tree].productID NOT IN (1040,1050,1168) THEN 1 ELSE 0 END) = 0

还有其他的，每个都有变体，但这应该给你一个很好的开始。但我真的会强调 GROUP BY 的使用和对 INDEXES 的考虑:)