查询存在PreliminaryTestReport但无FinalTestReport的RequestID及SQL问题排查

你的SQL语句失效的核心原因是子查询未关联外层的RequestID：原语句里的not exists判断的是整个表中是否存在FinalTestReport记录，而非当前RequestID对应的记录中是否存在。只要表内有任意一条FinalTestReport，这个条件就会过滤掉所有结果，自然查不到目标数据。

修正后的SQL语句 #

SELECT RequestID
FROM RequestDocumentSignature
WHERE SignatureDocumentType = 'PreliminaryTestReport'
AND NOT EXISTS (
    SELECT 1
    FROM RequestDocumentSignature AS inner_table
    WHERE inner_table.RequestID = RequestDocumentSignature.RequestID
    AND inner_table.SignatureDocumentType = 'FinalTestReport'
)
GROUP BY RequestID;

其他可选实现方式 #

• 分组条件聚合写法：通过统计每种类型的记录数来筛选

  SELECT RequestID
  FROM RequestDocumentSignature
  GROUP BY RequestID
  HAVING SUM(CASE WHEN SignatureDocumentType = 'PreliminaryTestReport' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN SignatureDocumentType = 'FinalTestReport' THEN 1 ELSE 0 END) = 0;
  

• 左连接筛选写法：利用左连接后匹配为空的特性判断不存在

  SELECT DISTINCT r1.RequestID
  FROM RequestDocumentSignature r1
  LEFT JOIN RequestDocumentSignature r2 
      ON r1.RequestID = r2.RequestID 
      AND r2.SignatureDocumentType = 'FinalTestReport'
  WHERE r1.SignatureDocumentType = 'PreliminaryTestReport'
  AND r2.RequestID IS NULL;
  

内容的提问来源于stack exchange，提问作者mhvoid