mysql - 将查询从 NOT IN 更改为 NOt EXISTS

Question

我对 VBA 编程有点陌生。我写的这段代码现在变慢了。我最近刚从后端表转移到 SQL 表。我正在使用的查询使用 NOT IN。我在某些地方读到使用 NOT EXISTS 可能会加快速度。这是我的代码。我需要将其转换为 NOT EXISTS，但我不确定如何:

SELECT BatchID
FROM TblBatchInfo
WHERE (IsNull([RSOutDateTime])=False) 
     AND (IsNull([HBDropDateTime])=False)
     AND (Format([BatchDateTime],"Short Date")=Format(Now(),"Short Date"))
     AND (IsNull(PSPassedOut=True) OR (PSPassedOut=""))
     AND LEN(LabelID)=9 
     AND [Area] <> 5
     AND [Area] <> 6
     AND [Area] <> 7
     AND (BatchID Not In (SELECT [BatchID]
                                          FROM [TblBatchInfo]
                                          WHERE (IsNull([RSOutDateTime]) = True)
                                           AND LEN(LabelID)=9
                                           AND [Area] <> 5 
                                           AND [Area] <> 6
                                           AND [Area] <> 7
                                           AND (Format([BatchDateTime], "Short Date") = Format(Now(), "Short Date"))))
GROUP BY BatchID
ORDER BY BatchID;

Answer 1

使用表别名是因为在子查询中您将引用两个表。你也可以做 NOT IN (5,6,7)而不是多个 <> .

SELECT BatchID
FROM TblBatchInfo x
WHERE (IsNull([RSOutDateTime])=False) 
     AND (IsNull([HBDropDateTime])=False)
     AND (Format([BatchDateTime],"Short Date")=Format(Now(),"Short Date"))
     AND (IsNull(PSPassedOut=True) OR (PSPassedOut=""))
     AND LEN(LabelID)=9 
     AND [Area] NOT IN (5,6,7)
     AND NOT EXISTS (SELECT 1 [BatchID]
                     FROM [TblBatchInfo] y
                     WHERE (IsNull([RSOutDateTime]) = True)
                         AND x.[BatchId] = y.BatchID
                         AND LEN(LabelID)=9
                         AND [Area] NOT IN (5,6,7) 
                         AND (Format([BatchDateTime], "Short Date") = Format(Now(), "Short Date"))
                    )
GROUP BY BatchID
ORDER BY BatchID;