如何筛选仅包含指定prod_code值的product_number对应记录

你原有SQL的筛选逻辑是行级判断，仅对单条记录的prod_code取值做校验，无法判断同一个product_number对应的所有记录的prod_code集合是否符合要求，逻辑本身存在缺陷。你当前测试得到的结果只是碰巧和预期一致，一旦业务表中出现某个product_number同时有CIOFB1和其他非CIOFB2/CIOFB3的prod_code取值，原有语句就会返回不符合要求的结果。

方案1：分组聚合关联法（兼容性最好，支持获取符合要求的product_number对应的全量记录） #

SELECT p.* 
FROM product_number p
INNER JOIN (
    SELECT product_number
    FROM product_number
    GROUP BY product_number
    HAVING SUM(CASE WHEN prod_code = 'CIOFB1' THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN prod_code IN ('CIOFB2', 'CIOFB3') THEN 1 ELSE 0 END) = 0
) t ON p.product_number = t.product_number

逻辑说明：

• 子查询先按product_number分组，通过聚合函数校验两个条件：该product_number至少存在1条prod_code为CIOFB1的记录，且不存在任何prod_code为CIOFB2、CIOFB3的记录
• 关联原表即可拿到符合要求的product_number对应的所有记录

方案2：NOT EXISTS法（逻辑更直观，适合仅需返回符合要求的CIOFB1记录的场景） #

SELECT * 
FROM product_number p1
WHERE p1.prod_code = 'CIOFB1'
AND NOT EXISTS (
    SELECT 1 
    FROM product_number p2 
    WHERE p2.product_number = p1.product_number 
    AND p2.prod_code IN ('CIOFB2', 'CIOFB3')
)

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