识别缺失记录的SQL查询(NOT IN子查询)

Question

我正在尝试编写一个查询，该查询将返回为文档类型6553生成的打印作业的每个ID(6553和6554之间的文档类型共享值)，但不是6554 -这些意味着总是一起生成。

我尝试了以下操作，这需要相当长的时间来运行，并产生了许多对我来说似乎太高的值:

select ID from PrintQueueShadow
where DocumentType = '6553'
and CreateDate > getdate() - 7  --No more than 7 days old
and ID not in
(select ID from PrintQueueShadow
where CreateDate > getdate() - 7
and DocumentType = '6554')  --Checking against every ID for Doc Type 6554s sent in the last 7 days

任何帮助都将非常感激。谢谢!

Answer 1

你的逻辑看起来是正确的，但是我们可以通过使用现有的逻辑来改进你的逻辑。此外，我们可以尝试建议一个索引，这可能会加快查询。

SELECT ID
FROM PrintQueueShadow pqs1
WHERE DocumentType = '6553' AND CreateDate > GETDATE() - 7 AND
NOT EXISTS (
SELECT 1
FROM PrintQueueShadow pqs2
WHERE pqs2.ID = pqs1.ID AND
CreateDate > GETDATE() - 7 AND 
DocumentType = '6554'
);

使用EXISTS可能比WHERE IN (...)性能更好，因为前者允许数据库在子查询中找到匹配的记录后立即停止搜索。

上面的查询可能受益于以下索引:

CREATE INDEX idx ON PrintQueueShadow (ID, DocumentType, CreateDate);

您也可以尝试上述索引中的三列的排列。

Answer 2

您可以尝试使用self - join。

SELECT DISTINCT PQS_1.ID 
FROM PrintQueueShadow AS PQS_1
LEFT JOIN PrintQueueShadow AS PQS_2
ON  PQS_1.ID = PQS_2.ID
AND PQS_2.CreateDate > GETDATE() - 7
AND PQS_2.DocumentType = '6554' --Checking against every ID for Doc Type 6554s sent in the last 7 days
WHERE PQS_1.DocumentType = '6553'
AND PQS_1.CreateDate > GETDATE() - 7  --No more than 7 days old
AND PQS_2.ID IS NULL -- Excluding ID which have been found in Doc Type 6553

我还添加了DISTINCT语句，以防在给定的DocumentType中有ID的重复