时间戳

首页 > 解决方案 > 如果条件在右表中满足而不使用子查询oracle，如何排除左表中的所有行

sql - 如果条件在右表中满足而不使用子查询oracle，如何排除左表中的所有行

问题描述

我有两个表，BATCH_MESSAGES，BATCH_INSTRUCTIONS，如果它的任何指令状态被暂停/拒绝，我想排除所有批次。

左边是 BATCH_MESSAGES，右边是 BATCH_INSTRUCTIONS。

这是 BATCH_MESSAGES 的 DDLBATCH_MESSAGES

这是针对：BATCH_INSTRUCTIONS

BATCH_INSTRUCTIONS

只应输出“CDC-SCBP-862411139626”，因为它是唯一一个所有指令状态为 NOT IN suspend/Rejected 的指令。

我的查询产生相同的结果，但它使用子查询复杂且效率低下。有更好的方法吗？

SELECT DISTINCT BM.ID,
         BM.BATCH_MSG_TYPE,
         BM.PARENT_BATCH_ID
FROM iris_activity.BATCH_MESSAGES BM 
WHERE BM.INSTRUCTIONS_COUNT = BM.PROCESSED_INSTRUCTIONS
AND BM.STAGE NOT IN ('COMPLETED')
AND BM.BATCH_MSG_TYPE NOT LIKE 'pacs.004%'
AND BM.RECEIVING_MODE = 'Stream' 
AND BM.ID NOT IN (SELECT DISTINCT BATCH_ID 
                  FROM iris_activity.BATCH_MESSAGES bm 
                  INNER JOIN Iris_activity.BATCH_INSTRUCTIONS bi  
                         on (bm.id = bi.BATCH_ID 
                            and bm.BATCH_MSG_TYPE = bi.BATCH_TYPE)
                   WHERE STATUS IN ('Suspended','Rejected') 
                   GROUP BY BATCH_ID, STATUS)
AND BM.RETRIES              <=5 ;

这可以更高效、更简单吗？

标签： sqloracleperformancesubqueryquery-optimization

解决方案

not exists如果你可以使用Not in

SELECT DISTINCT BM.ID,
         BM.BATCH_MSG_TYPE,
         BM.PARENT_BATCH_ID
FROM iris_activity.BATCH_MESSAGES BM 
WHERE BM.INSTRUCTIONS_COUNT = BM.PROCESSED_INSTRUCTIONS
AND BM.STAGE NOT IN ('COMPLETED')
AND BM.BATCH_MSG_TYPE NOT LIKE 'pacs.004%'
AND BM.RECEIVING_MODE = 'Stream' 
AND NOT EXISTS (SELECT 1
                  FROM Iris_activity.BATCH_INSTRUCTIONS bi  
                         WHERE  bm.id = bi.BATCH_ID 
                            and bm.BATCH_MSG_TYPE = bi.BATCH_TYPE
                            and STATUS IN ('Suspended','Rejected') 
                            )
AND BM.RETRIES              <=5 ;

推荐阅读