如何使用SQL筛选同时存在ISC和LLC记录的BATCH_ID？

表PM_BATCH_ST的数据： #

需求 #

筛选出同时存在ISC和LLC记录的BATCH_ID，期望结果：

• 1000564932
• 1000585739
• 1000384769

解决方案 #

以下是几种可行的SQL实现方式：

方法1：分组统计筛选

通过分组后统计不同RESULT的数量，筛选出包含两种目标结果的BATCH_ID：

SELECT BATCH_ID
FROM PM_BATCH_ST
WHERE RESULT IN ('ISC', 'LLC')
GROUP BY BATCH_ID
HAVING COUNT(DISTINCT RESULT) = 2;

方法2：自连接查询

将表与自身连接，匹配同一BATCH_ID下分别存在ISC和LLC的记录：

SELECT DISTINCT t1.BATCH_ID
FROM PM_BATCH_ST t1
JOIN PM_BATCH_ST t2 ON t1.BATCH_ID = t2.BATCH_ID
WHERE t1.RESULT = 'ISC' AND t2.RESULT = 'LLC';

方法3：EXISTS子查询

通过子查询判断每个BATCH_ID是否同时存在两种目标结果：

SELECT DISTINCT BATCH_ID
FROM PM_BATCH_ST t1
WHERE EXISTS (
    SELECT 1 FROM PM_BATCH_ST t2 
    WHERE t2.BATCH_ID = t1.BATCH_ID AND t2.RESULT = 'ISC'
)
AND EXISTS (
    SELECT 1 FROM PM_BATCH_ST t2 
    WHERE t2.BATCH_ID = t1.BATCH_ID AND t2.RESULT = 'LLC'
);

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