Oracle SQL查询需求：查找状态始终为FAILED的PRODUCER和CONSUMER配对

嘿，我来帮你搞定这个查询需求！你要找的是那些所有记录状态都是FAILED的PRODUCER和CONSUMER配对——换句话说，只要这个配对有哪怕一条STATUS是COMPLETE的记录，就不能被选出来。下面给你两种实用的实现方法：

方法一：GROUP BY + HAVING子句（直观易懂） #

这种方法通过分组统计来筛选符合条件的配对，逻辑非常直接：

SELECT PRODUCER, CONSUMER
FROM your_table_name
GROUP BY PRODUCER, CONSUMER
HAVING COUNT(CASE WHEN STATUS = 'COMPLETE' THEN 1 END) = 0
   AND COUNT(CASE WHEN STATUS = 'FAILED' THEN 1 END) > 0;

逻辑拆解： #

• GROUP BY PRODUCER, CONSUMER：把相同生产者和消费者的所有记录归为一组
• COUNT(CASE WHEN STATUS = 'COMPLETE' THEN 1 END) = 0：统计每组中状态为COMPLETE的记录数，等于0说明这个配对从未出现过成功记录
• COUNT(CASE WHEN STATUS = 'FAILED' THEN 1 END) > 0：确保这个配对至少有一条失败记录（避免筛选出无任何记录的空配对，根据你的实际数据也可以省略，但加上更严谨）

方法二：NOT EXISTS子查询（性能友好） #

如果你的表数据量很大，这种方法可能更高效——它一旦发现某个配对存在COMPLETE记录，就会立刻排除，不用做全量统计：

SELECT DISTINCT PRODUCER, CONSUMER
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t1.PRODUCER = t2.PRODUCER
      AND t1.CONSUMER = t2.CONSUMER
      AND t2.STATUS = 'COMPLETE'
)
AND EXISTS (
    SELECT 1
    FROM your_table_name t3
    WHERE t1.PRODUCER = t3.PRODUCER
      AND t1.CONSUMER = t3.CONSUMER
      AND t3.STATUS = 'FAILED'
);

逻辑拆解： #

• NOT EXISTS(...)：检查当前配对是否存在任何一条COMPLETE记录，不存在才保留
• EXISTS(...)：确保这个配对至少有一条FAILED记录
• DISTINCT：因为同一个配对可能有多条重复记录，用它去重，只返回唯一的配对组合

针对你给出的示例数据，这两个查询都会返回PRODUCER AAA和CONSUMER YYY，而AAA-XXX因为有一条COMPLETE记录，会被正确排除。记得把查询里的your_table_name替换成你实际的表名哦！

备注：内容来源于stack exchange，提问作者user3478415