sql-server - NOT IN 的 SQL 优化代码

Question

我继承了一个旧的 SQL 脚本，我想对其进行优化，但经过多次测试，我必须承认我的所有测试都只会创建带有重复 block 的巨大 SQL。我想知道是否有人可以为以下模式提出更好的代码(参见下面的代码)。我不想使用临时表(WITH)。为了简单起见，我只放了 3 个级别(表 TMP_C、TMP_D 和 TMP_E)，但原始 SQL 有 8 个级别。

WITH
TMP_A AS (
SELECT
 ID,
 Field_X
FROM A

TMP_B  AS(
SELECT DISTINCT
 ID,
 Field_Y,
 CASE
  WHEN Field_Z IN ('TEST_1','TEST_2') THEN 'CATEG_1'
  WHEN Field_Z IN ('TEST_3','TEST_4') THEN 'CATEG_2'
  WHEN Field_Z IN ('TEST_5','TEST_6') THEN 'CATEG_3'
  ELSE 'CATEG_4'
 END AS CATEG
FROM B
INNER JOIN TMP_A
ON TMP_A.ID=TMP_B.ID),

TMP_C AS (
SELECT DISTINCT 
 ID,
 CATEG
FROM TMP_B
WHERE CATEG='CATEG_1'),

TMP_D AS (
SELECT DISTINCT 
 ID,
 CATEG
FROM TMP_B
WHERE CATEG='CATEG_2' AND ID NOT IN (SELECT ID FROM TMP_C)),

TMP_E AS (
SELECT DISTINCT
 ID,
 CATEG
FROM TMP_B
WHERE CATEG='CATEG_3'
 AND ID NOT IN (SELECT ID FROM TMP_C)
 AND ID NOT IN (SELECT ID FROM TMP_D))

SELECT * FROM TMP_C
UNION
SELECT * FROM TMP_D
UNION
SELECT * FROM TMP_E

非常感谢您的帮助。

Answer 1

首先，选择 DISTINCT 将防止结果集中出现重复项，因此您会过度处理条件。通过添加“WITH”定义并尝试嵌套它们的使用，会使遵循起来更加困惑。数据最终全部来自“B”表，其中“A”中也有关键匹配。让我们从这个开始...并且由于您没有在结果集中使用 (B)Field_Y 或 (A)Field_X 中的任何内容，因此不要将它们添加到困惑中。

SELECT DISTINCT
      B.ID,
      CASE WHEN B.Field_Z IN ('TEST_1','TEST_2') THEN 'CATEG_1'
           WHEN B.Field_Z IN ('TEST_3','TEST_4') THEN 'CATEG_2'
           WHEN B.Field_Z IN ('TEST_5','TEST_6') THEN 'CATEG_3'
           ELSE 'CATEG_4'
           END AS CATEG
   FROM
      B  JOIN  A   ON  B.ID = A.ID
   WHERE
      B.Field_Z IN ( 'TEST_1', 'TEST_2', 'TEST_3', 'TEST_4', 'TEST_5', 'TEST_6' )

where 子句将仅包含您想要的那些类别限定值，并且仍然包含每个类别的结果。

现在，如果您实际上需要“Field_Y”或“Field_X”中的其他值，那么这将生成不同的查询。但是，您的 Tmp_C、Tmp_D 和 Tmp_E 无论如何都只要求 ID 和 CATEG 列。