Oracle SQL Developer无法筛选符合特定条件的行求助

原SQL问题分析 #

原SQL的NOT EXISTS子句逻辑存在缺陷：通过cri.CRI_KEY != valCri.CRI_KEY关联判断的方式，无法准确排除包含非指定条件的SubPerimeter，反而会错误过滤掉符合要求的记录。

正确实现思路 #

要精准筛选目标SubPerimeter，需同时满足：

• 该SubPerimeter的所有criteria均为类型IDENT且值为'00000028'或'0029428A'；
• 两个指定值的criteria都必须存在（避免仅包含其中一个的情况）。

修正后的SQL代码 #

WITH CRITERIA_SUMMARY AS (
    SELECT 
        sprm.SPRM_KEY,
        COUNT(*) AS total_cri,
        SUM(CASE WHEN cri.CRI_TYPE = 'IDENT' AND cri.CRI_VALUE IN ('00000028', '0029428A') THEN 1 ELSE 0 END) AS valid_cri_count
    FROM S_SUB_PERIMETER sprm
    LEFT JOIN S_CRITERIA cri ON sprm.SPRM_KEY = cri.SPRM_KEY
    GROUP BY sprm.SPRM_KEY
)
SELECT 
    sprm.SPRM_IDENTIFIER,
    sprm.SPRM_KEY
FROM S_SUB_PERIMETER sprm
JOIN CRITERIA_SUMMARY cs ON sprm.SPRM_KEY = cs.SPRM_KEY
-- 验证两个指定值都存在
JOIN S_CRITERIA cri1 ON sprm.SPRM_KEY = cri1.SPRM_KEY AND cri1.CRI_VALUE = '00000028'
JOIN S_CRITERIA cri2 ON sprm.SPRM_KEY = cri2.SPRM_KEY AND cri2.CRI_VALUE = '0029428A'
WHERE 
    cs.total_cri = cs.valid_cri_count  -- 无其他额外条件
    AND cs.valid_cri_count = 2         -- 恰好包含两个指定条件

适配不同数据库的补充方案 #

如果你的数据库支持集合函数（如PostgreSQL的ARRAY_AGG），可以用更简洁的方式验证值的集合：

WITH CRITERIA_SUMMARY AS (
    SELECT 
        sprm.SPRM_KEY,
        COUNT(*) AS total_cri,
        ARRAY_AGG(DISTINCT cri.CRI_VALUE) AS cri_values
    FROM S_SUB_PERIMETER sprm
    LEFT JOIN S_CRITERIA cri ON sprm.SPRM_KEY = cri.SPRM_KEY
    GROUP BY sprm.SPRM_KEY
)
SELECT 
    sprm.SPRM_IDENTIFIER,
    sprm.SPRM_KEY
FROM S_SUB_PERIMETER sprm
JOIN CRITERIA_SUMMARY cs ON sprm.SPRM_KEY = cs.SPRM_KEY
WHERE 
    cs.total_cri = 2
    AND cs.cri_values = ARRAY['00000028', '0029428A']
    AND EXISTS (SELECT 1 FROM S_CRITERIA cri WHERE cri.SPRM_KEY = sprm.SPRM_KEY AND cri.CRI_TYPE = 'IDENT')

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