Oracle SQL单表条件查询：基于IdentificationNo字段规则过滤结果集

核心逻辑 #

要实现要求的三个规则，我们需要：

1. 仅保留InformationType='abc'的行（对应规则2）；
2. 排除那些同时存在xyz类型且金额为0记录的IdentificationNo（对应规则1）；
3. 仅保留那些只有abc类型记录的IdentificationNo（对应规则3）。

实现代码 #

以下是基于NOT EXISTS的简洁写法，无需CTE，兼容性好：

SELECT t.IdentificationNo, t.InformationType, t.datefield, t.vauleAmount
FROM your_table t
WHERE t.InformationType = 'abc'
-- 排除存在xyz类型且金额为0的ID（触发规则1的情况）
AND NOT EXISTS (
    SELECT 1
    FROM your_table t1
    WHERE t1.IdentificationNo = t.IdentificationNo
    AND t1.InformationType = 'xyz'
    AND t1.vauleAmount = 0
)
-- 排除存在非abc类型记录的ID（仅保留只有abc行的ID，对应规则3）
AND NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.IdentificationNo = t.IdentificationNo
    AND t2.InformationType != 'abc'
);

示例测试 #

用你提供的示例数据测试时，将your_table替换为示例数据集即可验证结果：

WITH your_table AS (
    SELECT '123' AS IdentificationNo, 'abc' AS InformationType, DATE '1970-01-01' AS datefield, 100 AS vauleAmount FROM DUAL UNION ALL
    SELECT '123', 'xyz', DATE '1970-01-02',   0 FROM DUAL UNION ALL
    SELECT '123', 'xyz', DATE '1970-01-03', 200 FROM DUAL UNION ALL
    SELECT '123', 'def', DATE '1970-01-04', 300 FROM DUAL
)
SELECT t.*
FROM your_table t
WHERE t.InformationType = 'abc'
AND NOT EXISTS (
    SELECT 1
    FROM your_table t1
    WHERE t1.IdentificationNo = t.IdentificationNo
    AND t1.InformationType = 'xyz'
    AND t1.vauleAmount = 0
)
AND NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.IdentificationNo = t.IdentificationNo
    AND t2.InformationType != 'abc'
);

该查询会返回空结果集，符合规则1的要求。

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